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:
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

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:
FIND(""",B1)   // will NOT work!
FIND("""",B1)  // will work
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:
=MID(B1, 1, FIND("°", B1)-1)
In column D put the formula:
=MID(B1,FIND("°",B1)+1, FIND("′",B1)- FIND("°",B1) - 1)
In column E put the formula:
=MID(B1,FIND("′",B1)+1, FIND("″",B1)- FIND("′",B1) - 1)
In column F put the formula:
=MID(B1,FIND("″",B1)+1, 1)
In column G put the formula:
=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)
In column H put the formula:
=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:
=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:
=MID(B1,FIND("″",B1,FIND(F1,B1))+1,1)
In column K put the formula:
=C1 + (D1/60) + (E1/3600)
In column L put the formula:
=G1 + (H1/60) + (I1/3600)
In column M put the formula:
=IF(F1="N", K1, K1 * -1)
In column N put the formula:
=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:
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

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):
// 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)


Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.