How can I change 49:47:16,20 to 49°47'16.20" in large data set?

[pcvchriskmg]

Hello,

I have a large list of latitude and longitude points, but they are not in the correct format. For example, they are in the format : 49:47:16,20 as opposed to 49°47'16.20" (Google maps can only read the latter).

I recorded a small little macro that replaces the first colon ) with the degrees sign (°), the second colon ) with the hours (') and the comma (,) with a period (.) I then added the minutes (") at the end, When I run it, however, it replaces the numbers with the original numbers in the cell that I had recorded the macro on.

I also experimented a little bit with "Find and Replace" but that didn't work either.

What's a simple way for me to change the format of all these cells so that I have the correct latitude/longitude format?

Thanks in advance,
Chris

 

[JamesW]

Hi,

Try this custom format:

[hh]º mm' ss\"

 

[SteveO59L]

Is the format of your data always

nn:nn:nn,nn

?

 

[njimack]

Assuming your data is in column A of sheet1, starting from row 2...

Sub Latitude()
Dim Last_Row As Long
Dim r As Range
Dim s As String

Application.ScreenUpdating = False

With Sheets("Sheet1")

    Last_Row = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each r In Range(.Cells(2, 1), .Cells(Last_Row, 1))
        s = r.Value
        s = Replace(s, ":", "°", Count:=1)
        s = Replace(s, ":", "'")
        s = Replace(s, ",", ".")
        s = s & """"
        r = s
    
    Next r
    
End With

Application.ScreenUpdating = True

End Sub

 

[AlphaFrog]

If this 49:47:16,20 is in cell A1, try a formula like this...

=TEXT(SUBSTITUTE(TRIM(A1),",",".")*1,"[h]°mm'ss.00")

Result:
49°47'16.20

 

[pcvchriskmg]

Assuming your data is in column A of sheet1, starting from row 2...

Sub Latitude()
Dim Last_Row As Long
Dim r As Range
Dim s As String

Application.ScreenUpdating = False

With Sheets("Sheet1")

    Last_Row = Range("A" & Rows.Count).End(xlUp).Row
    
    For Each r In Range(.Cells(2, 1), .Cells(Last_Row, 1))
        s = r.Value
        s = Replace(s, ":", "°", Count:=1)
        s = Replace(s, ":", "'")
        s = Replace(s, ",", ".")
        s = s & """"
        r = s
    
    Next r
    
End With

Application.ScreenUpdating = True

End Sub

Thank you!

This works great.

Chris