Help converting Coordinates

[valhalla1]

Hello,
I have a spreadsheet that has a column filled with both the latitude and longitude in one cell. The Lat and long is separated by a "/" (eg. 55 32.4800 / -113 33.0480). The coordinates are currently in DD MM.mmm format and I would like them in DD.dddddd format and have would like to have Lat and Long in separate columns. Can anyone help me with this? Thanks in advance. I have attached the spreadsheet.

[63falcondude]

Can you include the desired result, which you can enter manually, for a few rows in your sample?

[Pete_UK]

This will get you the latitude:

=LEFT(C2,FIND(" ",C2)-1)+SUBSTITUTE(LEFT(C2,FIND("/",C2)-2),LEFT(C2,FIND(" ",C2)),"")/60

Hope this helps (just signing off now).

Pete

[valhalla1]

I attached tthe spreadsheet with a couple coordinates manually entered showing my desired result. Thanks

[valhalla1]

I got the Lat now thanks to Pete_UK, anyone else able to help with the long?

[63falcondude]

I have never converted coordinates before. Assuming that longitude is calculated the same way as latitude, try this (looking at the workbook from post #4):

=LEFT(MID(B2,FIND("/",B2)+2,100),FIND(" ",MID(B2,FIND("/",B2)+2,100))-1)-MID(MID(B2,FIND("/",B2)+2,100),FIND(" ",MID(B2,FIND("/",B2)+2,100))+1,100)/60

[Norie]

If split the slt_loc column using Data>Text to columns... with / as the delimiter you can then use the same formula for lat/long.

[63falcondude]

Alternatively, if the slt_loc will always be in the same format (i.e. ## ##.#### / -### ##.####), you can shorten the longitude formula from post #6 to this:

=MID(B2,14,4)-RIGHT(B2,7)/60

and the latitude formula to this:

=LEFT(B2,2)+MID(B2,4,7)/60

[MrShorty]

I like Norie's idea of using text to columns. I would probably expand the idea and split the text on both "/" and " " (space). Then you would get four columns where each coordinate is split into separate degree and decimal minutes columns. The conversion to decimal degrees is then a simple addition =degrees + decimal minutes/60.

[JohnTopley]

Try

Long

=(MID($C2,FIND(" /",$C2)+4,3)+(RIGHT(C2,7)/60))*-1

Lat

=LEFT(C2,FIND(" ",C2)-1)+MID(C2,FIND(" ",C2)+1,7)/60

[AlKey]

Alternatively, if the slt_loc will always be in the same format (i.e. ## ##.#### / -### ##.####), you can shorten the longitude formula from post #6 to this:

=MID(B2,14,4)-RIGHT(B2,7)/60

and the latitude formula to this:

=LEFT(B2,2)+MID(B2,4,7)/60

I like this approach.
BTY,it is easy to make a one formula with CHOOSE
Enter in D2 and copy across to C2 an down

Please Login or Register  to view this content.

[valhalla1]

thanks guys!

[FlameRetired]

Late comer.

I like the Norie's idea of Text to columns.

If formula is preferred you can build helper columns with this in D2 filled down and across column E.

Please Login or Register  to view this content.

Then in F2 filled down and across column G

Please Login or Register  to view this content.

[AliGW]

This very recent thread may be of interest: https://www.excelforum.com/excel-for...-lat-long.html