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.
Separating a column to make Lat/Long
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Separating a column to make Lat/Long
What would you like the result to be? Decimal degrees, e.g. 34°39′59″ becomes 34.66638889?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Separating a column to make Lat/Long
Yes, decimal degrees as that is what the power map requires.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Separating a column to make Lat/Long
It can be done with built-in functions, but the formulas would be complicated. I'd use custom VBA functions instead:
Use like this:
=Lat(D2)
and
=Lon(D2)
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
=Lat(D2)
and
=Lon(D2)
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Separating a column to make Lat/Long
For some reason, the Last comes out negative and the Long errors.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78493
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Separating a column to make Lat/Long
Sorry about that, it's because the space between latitude and longitude is a non-breaking space.
Corrected version:
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 254
- Joined: 09 May 2020, 14:00
Re: Separating a column to make Lat/Long
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.
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.