Converting from DMS to Decimal Degrees

**gortdaddy** · 06-23-2014, 04:10 AM

I have a list in the format 69°24'58.48"W, and I need a formula that not only converts, but also gives me a negative value when I have a W for long and S for lat. I'm having difficulties combining an If statement for this with MID and LEFT functions. Any help would be great, thank you.

**Richard Buttrey** · 06-23-2014, 04:50 AM

Hi and welcome to the forum.

You haven't shown us the full reference including latitude so it's not clear as to the exact text string. In addition we don't know whether if the degrees/minutes/seconds are less than 10 whether there is a preceding zero to fill out to 2 characters. I have assumed there might not be a leading zero hence the following is slightly more complicated than it needs to be if the string is always 13 characters long.

Formula:

Please Login or Register  to view this content.

**XOR LX** · 06-23-2014, 05:07 AM

Agreed with Richard that we need to know if the format is consistent.

I get a slightly different result, and to abbreviate the formula work I have made the assumption that the string will always be of the precise format:

xx°yy'yy.zz"A

(i.e. zeroes will be included where necessary, so that e.g. 9°4'58"W will actually be present as 09°04'58.00"W)

=SUMPRODUCT(0+MID(A1,FIND({"°","'",""""},A1)-{2,2,5},{2,2,5}),1/{1,60,3600})

Regards