Cannot convert dms to dd in Excel 2007 using VBA code

Question

I am trying to convert the data in an Excel sheet from dms (degree-minute-second) to dd (decimal degree) format using the vba script available here.

Here is sample data:

53° 0' 39.31" 6° 44' 50.14"
52° 59' 51.43" 6° 46' 0.02"

In the Excel sheet, this code is working:

=Convert_Decimal("10° 27' 36""")

but this is not working:

=Convert_Decimal(B3). 

The error is #value!

I want rows in excel in dd format. Alternatively I want to convert this Excel file into a database but the format must be dd.

Any suggestions anyone?

• Abhijit Gujar: You might be right but that is NOT my question. Extracting the value from one cell of excel is precisely where the error is ... i have to convert over 32000 rows.
• mkennedy: Are the longitude and latitude values in separate fields?
• Abhijit Gujar: yes dms lat and long are in separate column

Answer 1

Are the longitude and latitude values in separate fields? Because the Convert_Decimal function requires a single value.

After trying this myself, I think you were trying to convert the DMS values to DD in-place. That is, in the same column. Instead, use the script in a new column, referring to the one you want to convert. See image below where I have a DMS value in column C, and calculating its DD value into column B.

Microsoft Excel 2013 calculating a DD value from DMS

• mkennedy: Per gis.SE standards, question should probably be closed or migrated due to general (not really GIS-specific) content.
• Abhijit Gujar: mkenndy .. thanks for quick response and try . :) I appreciate .

Answer 2

It worked

I guess the problem was in the "degree" symbol which i replaced with "~" in the excel sheet and also in the vba code and bang !!! It worked !!!

reference:

https://www.youtube.com/watch?v=0QxxuSIFdFU

• mkennedy: Great! It's possible the original "degree" symbol wasn't really the correct one. There are characters that look like the degree symbol in some fonts and it's easy to confuse them.