Print

Excel: convert degrees minutes seconds to decimal

What?
A quick article on how to get the value of 40°4′20″N 116°35′51″E into 40.079857, 116.603112.

How?
Let's pretend all the names are in column A, in Column B I have the coordinates that I want to convert:
copyraw
A                                       B
-------------------------------------   ----------------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E
  1.  A                                       B 
  2.  -------------------------------------   ---------------------- 
  3.  Beijing Capital International Airport   40°420″N 116°3551″E 
  4.  Beijing Shahezhen Air Base              40°857″N 116°1917″E 
  5.  Beijing Tongxian Air Base               39°4840″N 116°4230″E 

Note that I tend to copy the coordinates off a website which has strange apostrophes and double-quotes. You can change this but remember to put two double-quotes if you are searching on it, eg:
copyraw
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
FIND("″",B1)   // will work
  1.  FIND(""",B1)   // will NOT work! 
  2.  FIND("""",B1)  // will work 
  3.  FIND("″",B1)   // will work 

You could merge all the statements into one but for simplicity's sake I'm putting each step here:

In column C put the formula:
copyraw
=MID(B1, 1, FIND("°", B1)-1)
  1.  =MID(B1, 1, FIND("°", B1)-1) 
In column D put the formula:
copyraw
=MID(B1,FIND("°",B1)+1, FIND("′",B1)- FIND("°",B1) - 1)
  1.  =MID(B1,FIND("°",B1)+1, FIND("′",B1)FIND("°",B1) - 1) 
In column E put the formula:
copyraw
=MID(B1,FIND("′",B1)+1, FIND("″",B1)- FIND("′",B1) - 1)
  1.  =MID(B1,FIND("′",B1)+1, FIND("″",B1)FIND("′",B1) - 1) 
In column F put the formula:
copyraw
=MID(B1,FIND("″",B1)+1, 1)
  1.  =MID(B1,FIND("″",B1)+1, 1) 
In column G put the formula:
copyraw
=MID(B1, FIND(F1, B1)+1, FIND("°", B1, FIND(F1,B1)) - FIND(F1,B1) - 1)

// if there is a space between the two
=MID(B1, FIND(F1, B1)+2, FIND("°", B1, FIND(F1,B1)) - FIND(F1,B1) - 2)
  1.  =MID(B1, FIND(F1, B1)+1, FIND("°", B1, FIND(F1,B1)) - FIND(F1,B1) - 1) 
  2.   
  3.  // if there is a space between the two 
  4.  =MID(B1, FIND(F1, B1)+2, FIND("°", B1, FIND(F1,B1)) - FIND(F1,B1) - 2) 
In column H put the formula:
copyraw
=MID(B1,FIND("°",B1,FIND(F1,B1))+1,FIND("′",B1,FIND(F1,B1))-FIND("°",B1,FIND(F1,B1))-1)
  1.  =MID(B1,FIND("°",B1,FIND(F1,B1))+1,FIND("′",B1,FIND(F1,B1))-FIND("°",B1,FIND(F1,B1))-1) 
In column I put the formula:
copyraw
=MID(B1,FIND("′",B1,FIND(F1,B1))+1,FIND("″",B1,FIND(F1,B1))-FIND("′",B1,FIND(F1,B1))-1)
  1.  =MID(B1,FIND("′",B1,FIND(F1,B1))+1,FIND("″",B1,FIND(F1,B1))-FIND("′",B1,FIND(F1,B1))-1) 
In column J put the formula:
copyraw
=MID(B1,FIND("″",B1,FIND(F1,B1))+1,1)
  1.  =MID(B1,FIND("″",B1,FIND(F1,B1))+1,1) 
In column K put the formula:
copyraw
=C1 + (D1/60) + (E1/3600)
  1.  =C1 + (D1/60) + (E1/3600) 
In column L put the formula:
copyraw
=G1 + (H1/60) + (I1/3600)
  1.  =G1 + (H1/60) + (I1/3600) 
In column M put the formula:
copyraw
=IF(F1="N", K1, K1 * -1)
  1.  =IF(F1="N", K1, K1 * -1) 
In column N put the formula:
copyraw
=IF(J1="E", L1, L1 * -1)
  1.  =IF(J1="E", L1, L1 * -1) 
There we go! Column M has your latitude and Column N has your longitude.

With the example above, this should return:
copyraw
A                                       B                       C       D       E       F       G       H       I       J       K               L               M               N
--------------------------------------  ----------------------  ------  ------  ------  ------  ------  ------  ------  ------  --------------  --------------  --------------  ------------
Beijing Capital International Airport   40°4′20″N 116°35′51″E   40      4       20      N       116     35      51      E       40.07222222     116.597500000   40.07222222     116.5975
Beijing Shahezhen Air Base              40°8′57″N 116°19′17″E   40      8       57      N       116     19      17      E       40.14916667     116.321388889   40.14916667     116.3213889
Beijing Tongxian Air Base               39°48′40″N 116°42′30″E  39      48      40      N       116     42      30      E       39.81111111     116.708333333   39.81111111     116.7083333
  1.  A                                       B                       C       D       E       F       G       H       I       J       K               L               M               N 
  2.  --------------------------------------  ----------------------  ------  ------  ------  ------  ------  ------  ------  ------  --------------  --------------  --------------  ------------ 
  3.  Beijing Capital International Airport   40°420″N 116°3551″E   40      4       20      N       116     35      51      E       40.07222222     116.597500000   40.07222222     116.5975 
  4.  Beijing Shahezhen Air Base              40°857″N 116°1917″E   40      8       57      N       116     19      17      E       40.14916667     116.321388889   40.14916667     116.3213889 
  5.  Beijing Tongxian Air Base               39°4840″N 116°4230″E  39      48      40      N       116     42      30      E       39.81111111     116.708333333   39.81111111     116.7083333 

Note that negative numbers should be supported. I tend to use Excel to reduce the decimal point to only 7 decimal places (for importing to a database where DECIMAL(10,7) ).

Merging the lot
If B1 contains the coordinates and there is a space after the first letter (N or S):
copyraw
// for latitude
=IF(MID(B1,FIND("″",B1)+1, 1)="N", (MID(B1, 1, FIND("°", B1)-1) + (MID(B1,FIND("°",B1)+1, FIND("′",B1)- FIND("°",B1) - 1)/60) + (MID(B1,FIND("′",B1)+1, FIND("″",B1)- FIND("′",B1) - 1)/3600)), (MID(B1, 1, FIND("°", B1)-1) + (MID(B1,FIND("°",B1)+1, FIND("′",B1)- FIND("°",B1) - 1)/60) + (MID(B1,FIND("′",B1)+1, FIND("″",B1)- FIND("′",B1) - 1)/3600)) * -1)

// for longitude
=IF(MID(B1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,1)="E", (MID(B1, FIND(MID(B1,FIND("″",B1)+1, 1), B1)+2, FIND("°", B1, FIND(MID(B1,FIND("″",B1)+1, 1),B1)) - FIND(MID(B1,FIND("″",B1)+1, 1),B1) -2) + (MID(B1,FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/60) + (MID(B1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/3600)), (MID(B1, FIND(MID(B1,FIND("″",B1)+1, 1), B1)+2, FIND("°", B1, FIND(MID(B1,FIND("″",B1)+1, 1),B1)) - FIND(MID(B1,FIND("″",B1)+1, 1),B1) -2) + (MID(B1,FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/60) + (MID(B1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/3600)) * -1)
  1.  // for latitude 
  2.  =IF(MID(B1,FIND("″",B1)+1, 1)="N", (MID(B1, 1, FIND("°", B1)-1) + (MID(B1,FIND("°",B1)+1, FIND("′",B1)FIND("°",B1) - 1)/60) + (MID(B1,FIND("′",B1)+1, FIND("″",B1)FIND("′",B1) - 1)/3600)), (MID(B1, 1, FIND("°", B1)-1) + (MID(B1,FIND("°",B1)+1, FIND("′",B1)FIND("°",B1) - 1)/60) + (MID(B1,FIND("′",B1)+1, FIND("″",B1)FIND("′",B1) - 1)/3600)) * -1) 
  3.   
  4.  // for longitude 
  5.  =IF(MID(B1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,1)="E", (MID(B1, FIND(MID(B1,FIND("″",B1)+1, 1), B1)+2, FIND("°", B1, FIND(MID(B1,FIND("″",B1)+1, 1),B1)) - FIND(MID(B1,FIND("″",B1)+1, 1),B1) -2) + (MID(B1,FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/60) + (MID(B1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/3600)), (MID(B1, FIND(MID(B1,FIND("″",B1)+1, 1), B1)+2, FIND("°", B1, FIND(MID(B1,FIND("″",B1)+1, 1),B1)) - FIND(MID(B1,FIND("″",B1)+1, 1),B1) -2) + (MID(B1,FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("°",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/60) + (MID(B1,FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))+1,FIND("″",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-FIND("′",B1,FIND(MID(B1,FIND("″",B1)+1, 1),B1))-1)/3600)) * -1) 

Category: Excel :: Article: 596