# Convert latitude and longitude coordinates in excel

I have about 10,000 coordinates I need to convert. The coordinates are in this format:

-116.6412722 34.7459017 -116.8066783 34.7734247

I need a way to convert them into this format, preferably in excel so I can do it in a batch:

N033.40.47.638 W117.51.48.032 N033.40.47.456 W117.51.47.881

I'm fairly new to excel, so if someone could help me out, I would greatly appreciate it.

-
Do you know how to convert from one form to another? Once you know the formula it shouldn't be hard to enter it into Excel. We can of course help you with the Excel bit if you're getting stuck somewhere, but have you looked into the formula at least? – Karan Jun 1 '13 at 5:21
I'm actually not sure of the formula. – dutchcoffee Jun 1 '13 at 5:29
The formula is easy -en.wikipedia.org/wiki/… – Alex P. Jun 1 '13 at 5:30
@AlexP. has pointed you in the right direction, but it really shouldn't have been too difficult to look up. – Karan Jun 1 '13 at 5:31

A basic set up (assuming headers in row 1) is:

Column A = Raw Data (e.g. -116.6412722 34.7459017)

Column B = Raw Long `=VALUE(LEFT(A2,FIND(" ",A2)-1))` This will split the raw data into just Long, and treat it as a number, rather than text.

Column C = Raw Lat `=VALUE(RIGHT(A2,LEN(A2)-FIND(" ",A2)))` This will split the raw data into just Lat, and treat it as a number, rather than text.

Column D = Converted Long in DMS `=IF(B2<0,"W","E")&TEXT(ABS((TRUNC(B2,0))),"0")&"."&TEXT((ABS(TRUNC((B2-TRUNC(B2,0))*60,0))),"0")&"."&TEXT((ABS(((B2-(TRUNC(B2,0)))*60)-TRUNC(((B2-(TRUNC(B2,0)))*60),0))*60),"0.0000")`

Column E = Converted Lat in DMS `=IF(C2<0,"S","N")&TEXT(ABS((TRUNC(C2,0))),"0")&"."&TEXT((ABS(TRUNC((C2-TRUNC(C2,0))*60,0))),"0")&"."&TEXT((ABS(((C2-(TRUNC(C2,0)))*60)-TRUNC(((C2-(TRUNC(C2,0)))*60),0))*60),"0.0000")`

Then, fill down as necessary.

You can do this with varying numbers of helper columns, if you want to troubleshoot possible errors at each step. Also, your sample data seemed to calculate differently, so I'm not sure where your numbers were coming from.

-
+1 For nice explanation. :-) – zekk Jun 1 '13 at 22:50
@TiagoCA Thanks, it turned out a bit more complicated than I expected trying to keep the number of columns to a minimum. VBA might have actually been a cleaner solution. – dav Jun 1 '13 at 22:53
Worked beautifully. Thank you!! – dutchcoffee Jun 2 '13 at 20:29