Jump to content

Splitting Coordinate to Lat, Lon with Calculation

This topic is 6446 days old. Please don't post here. Open a new topic instead.

Recommended Posts

[color:green]Hello all :yay:

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} :P

[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? :confused:

Thanks for your input in advance

Link to comment
Share on other sites

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]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. :bang:

[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: :thankyou:

Link to comment
Share on other sites

Use the Filter Function.

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


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")



Link to comment
Share on other sites

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?


Link to comment
Share on other sites

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?)

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

This topic is 6446 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.