Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Convert Latitude and longitude to decimal degrees

1. I agreed to these rules
Join Date
7th January 2011
Posts
4

## Convert Latitude and longitude to decimal degrees

Hi, I am trying to convert a large number of lats & long to dec deg. My values are landing in excel in the following format in a single cell: -43° 16'0.40"
Any help with this would be much appreciated!
Last edited by AAE; January 7th, 2011 at 21:02. Reason: revise thread title

Excel Video Tutorials / Excel Dashboards Reports

2. Development Team Member
Join Date
17th November 2005
Location
North East Pennsylvania, USA
Posts
696

## Re: Lat long conversion

acorbett,

Welcome to the Ozgrid forum.

See:

convert some latitude and longitude coordinates from "Degrees, Decimal Minutes" format to "Decimal Degrees" format
How to convert degrees/minutes/seconds angles to or from decimal angles in Excel 2000
http://support.microsoft.com/kb/213449

Have a great day,
Stan

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
7th January 2011
Posts
4

## Re: Lat long conversion

Thanks Stan - trouble is I am very "green"with Excel... I get as far as the Alt F11 line to open Visual Basic Editor but nothing happens...

Excel Video Tutorials / Excel Dashboards Reports

4. Development Team Member
Join Date
17th November 2005
Location
North East Pennsylvania, USA
Posts
696

## Re: Lat long conversion

acorbett,

What version of Excel are you using?

Please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

Have a great day,
Stan

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
7th January 2011
Posts
4

## Re: Lat long conversion

Thanks again Stan. I have attached a copy of what I have ~lats & longs (and a depth). I need to convert to decimal lats & longs (and depth) for export to some mapping sw.
Cheers,

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: Convert Latitude and longitude to decimal degrees

acorbett,

Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

• The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
• The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
• The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different

7. I agreed to these rules
Join Date
7th January 2011
Posts
4

## Re: Convert Latitude and longitude to decimal degrees

How would you prefer I titled this thread???

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: Convert Latitude and longitude to decimal degrees

VB:
```Function DMS2Deg(ByVal sInp As String) As Double
' Converts a string like [+|-]12° 32’ 28" to decimal degrees
' The symbols don't matter; there just have to be three number groups,
' with one or more spaces between, optionally preceded by a sign.

Dim sSgn        As Long
Dim i           As Long
Dim avs         As Variant

sSgn = IIf(Left(WorksheetFunction.Trim(sInp), 1) = "-", -1, 1)

For i = 1 To Len(sInp)
If InStr(" .0123456789", Mid(sInp, i, 1)) = 0 Then Mid(sInp, i) = " "
Next i

avs = Split(WorksheetFunction.Trim(sInp), " ")
DMS2Deg = sSgn * (avs(0) + avs(1) / 60# + avs(2) / 3600#)
End Function

```
Adding a Macro to a Code Module
1. Copy the code from the post
2. Press Alt+F11 to open the Visual Basic Editor (VBE)
3. From the menu bar in the VBE window, do Insert > Module
4. Paste the code in the window that opens

Then with -43° 16'0.40" in A1,

=DMS2Deg(A1) returns -43.2667777777778

9. Development Team Member
Join Date
17th November 2005
Location
North East Pennsylvania, USA
Posts
696

## Re: Convert Latitude and longitude to decimal degrees

acorbett,

Please explain how you get the value 0.2 in cell C3?

Have a great day and weekend,
Stan

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno