Separating a column to make Lat/Long

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Separating a column to make Lat/Long

Post by gailb »

How can I separate this into two columns to get my Lat and Long?

34°39′59″N 099°16′05″W

I tried
=LEFT(D2,FIND("N",D2)-1) and
=MID(D2,FIND("N",D2)+2,LEN(D2))

but this is not working. I need to convert this column of coordinates so I can put it in 3D power maps.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Separating a column to make Lat/Long

Post by HansV »

What would you like the result to be? Decimal degrees, e.g. 34°39′59″ becomes 34.66638889?
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Separating a column to make Lat/Long

Post by gailb »

Yes, decimal degrees as that is what the power map requires.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Separating a column to make Lat/Long

Post by HansV »

It can be done with built-in functions, but the formulas would be complicated. I'd use custom VBA functions instead:

Code: Select all

Function Lat(ByVal s As String) As Double
    s = Split(s)(0)
    Lat = Deg2Dec(s)
End Function

Function Lon(ByVal s As String) As Double
    s = Split(s)(1)
    Lon = Deg2Dec(s)
End Function

Function Deg2Dec(s As String) As Double
    Dim p1 As Long
    Dim p2 As Long
    Dim p3 As Long
    Dim d As Double
    p1 = InStr(s, "°")
    p2 = InStr(s, ChrW(8242))
    p3 = InStr(s, ChrW(8243))
    d = Left(s, p1 - 1) + Mid(s, p1 + 1, p2 - p1 - 1) / 60 + Mid(s, p2 + 1, p3 - p2 - 1) / 3600
    Select Case Right(s, 1)
        Case "S", "W"
            d = -d
    End Select
    Deg2Dec = d
End Function
Use like this:

=Lat(D2)

and

=Lon(D2)
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Separating a column to make Lat/Long

Post by gailb »

For some reason, the Last comes out negative and the Long errors.
Coordinates.xlsm
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Separating a column to make Lat/Long

Post by HansV »

Sorry about that, it's because the space between latitude and longitude is a non-breaking space.

Corrected version:

Code: Select all

Function Lat(ByVal s As String) As Double
    s = Replace(s, Chr(160), " ")
    s = Split(s)(0)
    Lat = Deg2Dec(s)
End Function

Function Lon(ByVal s As String) As Double
    s = Replace(s, Chr(160), " ")
    s = Split(s)(1)
    Lon = Deg2Dec(s)
End Function

Function Deg2Dec(s As String) As Double
    Dim p1 As Long
    Dim p2 As Long
    Dim p3 As Long
    Dim d As Double
    p1 = InStr(s, "°")
    p2 = InStr(s, ChrW(8242))
    p3 = InStr(s, ChrW(8243))
    d = Left(s, p1 - 1) + Mid(s, p1 + 1, p2 - p1 - 1) / 60 + Mid(s, p2 + 1, p3 - p2 - 1) / 3600
    Select Case Right(s, 1)
        Case "S", "W"
            d = -d
    End Select
    Deg2Dec = d
End Function
Coordinates.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Separating a column to make Lat/Long

Post by gailb »

Thanks Hans. It worked great on my entire column of data minus one location. Easy Google search and it's done. Thanks again.

I was messing around with the text to columns and then trying to build the formula that is basically in your UDF, but it was a pain.