1

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?

  • 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. – Abhijit Gujar Aug 18 '14 at 19:07
  • Are the longitude and latitude values in separate fields? – mkennedy Aug 18 '14 at 19:31
  • yes dms lat and long are in separate column – Abhijit Gujar Aug 18 '14 at 19:36
4

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

  • Per gis.SE standards, question should probably be closed or migrated due to general (not really GIS-specific) content. – mkennedy Aug 18 '14 at 19:41
  • mkenndy .. thanks for quick response and try . :) I appreciate . – Abhijit Gujar Aug 18 '14 at 19:53
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.