Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
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… – 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
up vote 1 down vote accepted

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.

share|improve this answer
+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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .