help with coordinate conversion

[valhalla1]

Hello,

I have a spreadsheet with coordinates in the following format. N54 36 10 W124 04 32.

I would like to be able to change the format of these coordinates to the following 54.6144444, 124.0666667

So would need to remove the N, and the W and convert the format.

Any help is appreciated and spreadsheet is attached

[MrShorty]

Are the coordinates consistently in that format? What is the meaning of each element? I assumed they were degrees minutes seconds, but I do not get the same values that you get. (I got 54.6028 for 54 36 10 and 124.0756 for 124 04 32).

Whatever the specific meaning of each field is, the difficult part is using Excel's text functions to parse the string into individual values. (MID() function help file https://support.office.com/en-us/art...8-4ecb12433028 ). Something like =MID(A2,2,2) will extract the "degrees" number 54. MID(A2,5,2) will extract the minutes 36. MID(A2,8,2) will extract the seconds 10. Combine that with appropriate "conversion factors" (minutes/60 and seconds/3600) to convert minutes and seconds to decimal degrees should do the job.

[valhalla1]

Are the coordinates consistently in that format? What is the meaning of each element? I assumed they were degrees minutes seconds, but I do not get the same values that you get. (I got 54.6028 for 54 36 10 and 124.0756 for 124 04 32).

Whatever the specific meaning of each field is, the difficult part is using Excel's text functions to parse the string into individual values. (MID() function help file https://support.office.com/en-us/art...8-4ecb12433028 ). Something like =MID(A2,2,2) will extract the "degrees" number 54. MID(A2,5,2) will extract the minutes 36. MID(A2,8,2) will extract the seconds 10. Combine that with appropriate "conversion factors" (minutes/60 and seconds/3600) to convert minutes and seconds to decimal degrees should do the job.

Thanks for your help, my math was wrong, you numbers are correct. I numbers are origonally in Degree Minute Second format. I am wanting degree.decimal format. I will play with your formulas and see if I can make it work, thanks.

[valhalla1]

Are the coordinates consistently in that format? What is the meaning of each element? I assumed they were degrees minutes seconds, but I do not get the same values that you get. (I got 54.6028 for 54 36 10 and 124.0756 for 124 04 32).

Whatever the specific meaning of each field is, the difficult part is using Excel's text functions to parse the string into individual values. (MID() function help file https://support.office.com/en-us/art...8-4ecb12433028 ). Something like =MID(A2,2,2) will extract the "degrees" number 54. MID(A2,5,2) will extract the minutes 36. MID(A2,8,2) will extract the seconds 10. Combine that with appropriate "conversion factors" (minutes/60 and seconds/3600) to convert minutes and seconds to decimal degrees should do the job.

Thanks for the help! I got it with this formula. =MID(A2,2,2)+(MID(A2,5,2))/60+(MID(A2,8,2))/3600

[D.Lovell]

Please Login or Register  to view this content.

[D.Lovell]

But if the coordinates are N12 vs N123, you need to account for this in the extraction from the string