Formatting Lat/Long Info

Hi there,

Spanking new to the forum here, sorry if this isn't the right place to post. Figured this might be a VBA task...

I need to reformat Lat Long data as follows:

Source Cell contains, as an example:

36 33 24.67849(N) 105 25 01.00930(W)

I need to generate 2 cells with a format:

36332468N and 105250101W

Note the need to round the decimal seconds to 2 places, then remove the decimal point.

I'm not much of a programmer, but would rather avoid brute forcing this by hand to avoid data entry errors.

I appreciate the help, and if theres a better forum for this question, I'm all about reposting elsewhere. Thanks!

Hi BeaconSlash,

Here is a workbook with code in it that formats the Latitudes and Longitudes per your description. The source cells are in Column A, the formatted longitude is in Column B and the formatted latitude is put in Column C.

Hope it helps,

Dan

Hi there,

When I open it, all I see is:

Cell A1: 36 33 24.67849(N) 105 25 01.00930(W)
Cell A2: 36 33 24.67849(S) 105 25 01.00930(E)

And no forumulas to be seen.

UDF
Use in cell like
=LatLng(A1)

Function LatLng(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d+(\.\d+)?)\(([NS])\)(\d+(\.\d+)?)\(([WE]).*"
        If .test(Replace(txt, " ", "")) Then
            txt = Replace(txt, " ", "")
            LatLng = .Replace(txt, Application.Round(Val(.Replace(txt, "$1")), 2) * 100 & _
                "$3 and " & Application.Round(Val(.Replace(txt, "$4")), 2) * 100 & "$6")
        End If
    End With
End Function

I admire how literal you were and apologize for not being more precise in my definition.

Arbitrary cell (A1) containing 36 33 24.67849(N) 105 25 01.00930(W) must be converted to two separate arbitrary cells (A2 B2), where arbitrary A2 contains the Lat portion, and arbitrary B2 contains the Long portion.

The VBA spits out appropriate values it seems. It feels like I would need one UDF like a =Lat(A1) and a =Lng(A1) to place in to separate cells.

Then

C1:D1
=LatLng(A1)
Confirm with Ctrl + Shift + Enter (array formula entry)

Function LatLng(ByVal txt As String)
    With CreateObject("VBScript.RegExp")
        .Pattern = "(\d+(\.\d+)?)\(([NS])\)(\d+(\.\d+)?)\(([WE]).*"
        If .test(Replace(txt, " ", "")) Then
            txt = Replace(txt, " ", "")
            LatLng = Split(.Replace(txt, Application.Round(Val(.Replace(txt, "$1")), 2) * 100 & _
                "$3^" & Application.Round(Val(.Replace(txt, "$4")), 2) * 100 & "$6"), "^")
        End If
    End With
End Function

Thank you very much for your help!