Convert DMS value 35°13'25.6"N 50°33'05.2"E to decimal degrees

[Drummer]

Hello Friends,
Need your help in converting the DMS value 35°13'25.6"N 50°33'05.2"E (mentioned in a Single Cell) -
1- to A single cell with both the LatLong in Decimal Degrees, and
2 -to two separate cells (Lat-Long in separe cells) in decimal degrees.

I could see an older discussion in a similar conversion but the DMS LatLongs were mentioned in 2 separate cell back then - which could not help in my case.
(I am having a huge list of such co-ordinates, hence I cannot split my initial value in 2 cells manuall to apply the previous solution)
Any help is greatly appreciated

 

[mole999]

https://support.microsoft.com/en-gb/kb/213449

but you might easily use concatenate to bring values together or LEFT and RIGHT to seperate, what ever you do you'll need three columns (IMHO)

 

[Drummer]

Yes, I did manage to separate them with Data(Text to Column)
But the formula which I found for a related query is yielding an inaccurate result.

i.e 24°13'25.6"N 55°33'05.2"E gives 24.220222N , 55.5408056E (which is inaccurate)

PS: I have used the formula "=SUBSTITUTE(SUBSTITUTE(LEFT(A2,LEN(A2)-3),"D ",":"),"' ",":")*IF(RIGHT(B2,1)="S",-24,24) "
provided on http://www.mrexcel.com/forum/excel-...ree-min-sec-decimal-degree-lat-longitude.html

 

[Tetra201]

Try these formulas:

For latitude =TEXT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH(" ",A2)-3),"°",""),"'",""),"#\:00\:00.0")*24&RIGHT(LEFT(A2,SEARCH(" ",A2)-1))
For longitude =TEXT(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH(" ",A2)+1,LEN(A2)-SEARCH(" ",A2)-2),"°",""),"'",""),"#\:00\:00.0")*24&RIGHT(A2,1)

Result: 35°13'25.6"N 50°33'05.2"E --> 35.2237777777778N and 50.5514444444444E

 

[Drummer]

Hello Tetra,
Thanks for trying to help
The output still doesn't match as good as compared with the formula on wikipedia (https://en.wikipedia.org/wiki/Decimal_degrees)

 

[Drummer]

Hello Tetra,
The output still doesn't match as good as compared with the formula on wikipedia (https://en.wikipedia.org/wiki/Decimal_degrees)

 

[Drummer]

Thanks guys for trying to help me..
But i was pretty overwhelmed with the direct formulas I found so far. And given my level of inexperience with scripting, I was quite skeptic.
So, finally I figured out an easily way for a beginner like me ..
1. Separate the Degree, Minute, Second and direction by using Data(Text to Column) into separate columns
2. Apply the formula D(Dec)=D + Min/60 + Sec/3600

=ROUND(SUM(B2+C2/60+D2/3600),5)&E2
where B2 = Degree, C2 = Min, D2 = Sec and E2 is Direction (N/S/E/W from the original value)
Looks quite basic and unsavvy, but gives out the accurate value.

 

[Tetra201]

... The output still doesn't match as good as compared with the formula on wikipedia...

If you are concerned with too many decimal positions, here are the formulas that round them to just four:

=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(LEFT(A2,SEARCH(" ",A2)-3),"°",""),"'",""),"#\:00\:00.0")*24,"0.0000")&RIGHT(LEFT(A2,SEARCH(" ",A2)-1))
=TEXT(TEXT(SUBSTITUTE(SUBSTITUTE(MID(A2,SEARCH(" ",A2)+1,LEN(A2)-SEARCH(" ",A2)-2),"°",""),"'",""),"#\:00\:00.0")*24,"0.0000")&RIGHT(A2,1)

Wikipedia example: 38°53'23"N 77°00'32"W --> 38.8897°, -77.0089°
My formulas: 38°53'23"N 77°00'32"W --> 38.8897N, 77.0089W

To me this looks like a pretty good match.