Text Manipulation Puzzle

[bleuspam]

I am given a spreadsheet with 1 column. In this column, is a long string containing a variable list of gps coordinates. The syntax of the string is always the same, like this:

LINESTRING(lat lon, lat lon, lat lon)

My problem is that I need to switch each coordinate to be lon lat instead of lat lon

Example:
LINESTRING(40.729647749011 -111.83158925119,40.729647749011 -111.82776993746,40.72675329185 -111.82776993746,40.72675329185 -111.83158925119,40.729647749011 -111.83158925119)

needs to be manipulated to be,
LINESTRING(-111.83158925119 40.729647749011,-111.83158925119 40.72675329185,-111.82776993746 40.72675329185,-111.82776993746 40.729647749011,-111.83158925119 40.729647749011)

Any takers? I've been trying a UDF in VBA... haven't had any success.

I've been able to identify these characteristics though,

• first coordinate always begins with a (
• last coordinate always ends with a )
• the number of coordinates can be identified by counting the spaces
• coordinates end with a comma (except for the last one, of course!)
• coordinates are not always the same length! trailing zeros would be dropped if a coordinate was 40.72647749000 it would be displayed as 40.72647749

This solution could prove to help a lot of people. There is an existing problem with Microsoft's SQL Server that expects lon lat while the majority of other spatial systems export into a format of lat lon - so there are plenty of forums out there but I've spent hours searching, and no solution was found (other than third party software).


I use Excel 2010, Visual Studio 2010, SQL Server 2008 R2

Sample
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]LINESTRING(40.729647749011 -111.83158925119,40.729647749011 -111.82776993746,40.72675329185 -111.82776993746,40.72675329185 -111.83158925119,40.729647749011 -111.83158925119)
[/TD]
[/TR]
[TR]
[TD]LINESTRING(40.690679858029 -112.02458551084,40.690708330652 -112.0224933878,40.69148928929 -112.02243974363,40.691468951942 -112.02455868875)
[/TD]
[/TR]
[TR]
[TD]LINESTRING(40.690679858029 -112.02458551084)
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

This macro should work, but to be safe, try it out on a copy of your data first as the output overwrites the original data (which is what I assumed you wanted to do).

Sub ReverseLatLon()
  Dim X As Long, Z As Long, LastRow As Long, CellVal As String, Beginning As String, Coords() As String
  Const DataCol As String = "A"
  Const StartRow As Long = 2
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  For X = StartRow To LastRow
    CellVal = Cells(X, DataCol).Value
    Beginning = Left(CellVal, InStr(CellVal, "("))
    Coords = Split(Replace(Replace(CellVal, Beginning, ""), ")", ""), ",")
    For Z = 0 To UBound(Coords)
      Coords(Z) = Mid(Coords(Z) & " " & Coords(Z), InStr(Coords(Z), " ") + 1, Len(Coords(Z)))
    Next
    Cells(X, DataCol).Value = Beginning & Join(Coords, ",") & ")"
  Next
End Sub

Note: There are two constants (the Const statements) that you have to change to match your actual layout. The DataCol constant is the column letter where you have the text you showed us and StartRow is the row number with the first one of those texts in it.

 

[b.downey]

Here is a UDF that will accomplish the task

Option Explicit
Function XFormGPS(s) As String
    Dim I As Integer
    Dim StartPos As Long
    
    Dim LatLong As Variant
    Dim Cord As Variant
    Dim Result As String
    
    Result = "LINESTRING("
    StartPos = InStr(s, "(")
    If StartPos > 0 Then
        s = Trim(Mid$(s, StartPos + 1))
    End If
    s = Mid$(s, 1, Len(s) - 1)
    
    LatLong = Split(s, ",")
    For I = 0 To UBound(LatLong)
        Cord = Split(LatLong(I), "-")
        Result = Result & Cord(1) & " - " & Cord(0) & ", "
    Next I
    XFormGPS = Result & ")"
End Function

 

[bleuspam]

I must say I'm impressed at the speed of your responses!

The macro does just fine, seems to be perfect. I like the idea of a UDF from b.downey, but it's slighly off. It is missing the negative sign when it does the flip. But there is enough here that I can piece together the final solution between the two of them.

Thanks!

 

[Rick Rothstein]

I like the idea of a UDF from b.downey, but it's slighly off.

A UDF it is. Here is my macro reworked into a UDF...

Function ReverseLatLon(S As String) As String
  Dim X As Long, Beginning As String, Ending As String, Coords() As String
  If S = "" Then Exit Function
  Beginning = Left(S, InStr(S, "("))
  Ending = Mid(S, InStr(S, ")"))
  Coords = Split(Replace(Replace(S, Beginning, ""), Ending, ""), ",")
  For X = 0 To UBound(Coords)
    Coords(X) = Mid(Coords(X) & " " & Coords(X), InStr(Coords(X), " ") + 1, Len(Coords(X)))
  Next
  ReverseLatLon = Beginning & Join(Coords, ",") & Ending
End Function

 

[b.downey]

Sorry, I misunderstod the requirement. I thought the "-" was a seperator between lat and lon. here is the revision

Function XFormGPS(s) As String
    Dim I As Integer
    Dim StartPos As Long
    
    Dim LatLong As Variant
    Dim Cord As Variant
    Dim Result As String
    s = "LINESTRING(40.729647749011 -111.83158925119,40.729647749011 -111.82776993746,40.72675329185 -111.82776993746,40.72675329185 -111.83158925119,40.729647749011 -111.83158925119)"

    Result = "LINESTRING("
    StartPos = InStr(s, "(")
    If StartPos > 0 Then
        s = Trim(Mid$(s, StartPos + 1))
    End If
    s = Mid$(s, 1, Len(s) - 1)
    
    LatLong = Split(s, ",")
    For I = 0 To UBound(LatLong)
        Cord = Split(LatLong(I), " ")
        Result = Result & Cord(1) & " " & Cord(0) & ", "
    Next I
 
    XFormGPS = Result & ")"
End Function