Splitting Coordinate to Lat, Lon with Calculation

[chuckcou]

[color:green]Hello all

I have a field called Coordinate with this value:

[color:red]N40° 1.694', W121° 30.999'

[color:green]I want to do a calculation, two field and put N40° 1.694' in Latitude Field and W121° 30.999' in Longitude field.

The below kinda works, but there must be a better idea out there.

For the Longitude I used this Calculation:

Middle( Coordinate; Position( Coordinate; ","; 0; 1 ) + 1; Position(Coordinate & "¶"; "¶"; Position( Coordinate; ","; 0; 1 ); 1 ) - Position( Coordinate; ","; 0; 1 ) - 1 )

{I used the Comma as the dividing place. Probably not the best idea because what if somehow there was another comma}

[color:green]So, what can I do to have it recongize the N and capture the N to the ' for Latitude?

And for the other field use the W and capture to the ' for Longitude?

Thanks for your input in advance

[Lee Smith]

Lat

LeftWords(YourFieldText, 2)

Long

RightWords(YourFieldText, 2)

HTH

Lee

[chuckcou]

Thanks Lee. That was an easy way. But as I research it more, I am going to do something a little different. Lets take this Latitude>>>

[color:red]N39° 50' 46.64

[color:brown][color:red]Break it up into 4 Fields

[color:blue]Field1(NorS)

[color:brown]Field2(Degrees)

[color:red]Field3(Minutes)

[color:green]Field4(Seconds)

[color:blue]For Field1(NorS) I used this calculation>>>

Left ( Latitude ; 1 )

[color:black]For Field2 And Field3 I need a little more help in finding the calculations.

[color:brown]For field2

If I use LeftWords ( Latitude ;1 ) , then I get the N in the result. I only want the number which will between 0 and 90. In this example the correct result should be 39

Thus I need some help with this concept and forumla? :qwery:

For Field3(Minutes)

The result should be 50 without the "'" mark and will be between 0 and 60. I would think you would have to use the middleWords function somehow.

Really need some help on this one. Not even sure where to begin.

[color:green]For Field4(Seconds)

I used this calculation

RightWords ( Latitude ; 1 )

(This works Great because it actually remove the "'" mark, too)

Thanks again for you feedback, suggestion, and help. :thankyou:

[Lee Smith]

Use the Filter Function.

Field1Filter( LeftWords ( Latitude ; 1 ); "0123456789")

Field2

Filter( MiddleWords ( Latitude ; 2 ; 1 ); "0123456789")

Field3 (Calculation, Text Result) =

Left (RightWords ( Latitude ; 1 ); 2)

Field4 (Calculation, Text Result) =

Filter( Right ( Latitude ; Left ( Latitude ; 2 ) ); "0123456789")

HTH

Lee

[chuckcou]

Thank you very much for your feedback. I learned something from your examples.

I just have one more question. Is it possible to find a specify symbol as a dividing point.

Let say we have this number:

N40° 1.694'% W121° 30.999 Record 1

N40 39' 20" % W121 30' 11" Record 2

N40 % W121 Record 3

The only common symbol within the above examples is the "%"

Let say, I want to put everything to the left of the "%" in one field and everything to the right of the % in another field.

Can we do that if we don't know how many words or character there are.

Probably we would use the Middle, but how to you choose the % sign as the dividing point?

:titanic:

[BobWeaver]

Check out the position function. It will find the position of any specific occurrence of a text string (or single character) in a larger text string. That may be more reliable if some of the items in your coordinate field are not always present. (Eg., if you have exactly 23 degrees and 0 minutes, do you still see 0 minutes or does the minutes part vanish?)

[ino]

:bye: Hi chuckcou

be careful, read att.,

regard's

Ino

Ways_of_writing_coordinates.zip

[BobWeaver]

I agree. There are a lot of ways that coordinates can be entered, especially if you let the user enter them manually without strict validation. However, it is possible to decipher the data as long as there are no ambiguities in it. For example, if the part that follows the degrees part doesn't end with a " or ' what assumption do we make about it? If we assume that this part is always minutes, then it's okay. See the attached file for an example file that makes very few assumptions about the data. I don't guarantee it to work in every possible situation, but it works for the examples given, and should work for many other combinations of input.

CoordParse.fp7.zip