Remove multiple spaces (VBA)

simurq

Board Regular
Joined
Nov 11, 2011
Messages
73
Here is the string (oCoord As String) I'm working on:
Code:
[COLOR=#ff0000]N[/COLOR]37[COLOR=#ff0000]°[/COLOR] 8[COLOR=#ff0000]'[/COLOR] 30.52[COLOR=#ff0000]"[/COLOR],[COLOR=#ff0000]W[/COLOR]107[COLOR=#ff0000]°[/COLOR] 45[COLOR=#ff0000]'[/COLOR] 46.92[COLOR=#ff0000]"[/COLOR],[COLOR=#ff0000]+[/COLOR]006682.00

Now, I want to modify it so that all characters highlighted in red are removed like this:

Code:
37 8 30.52,107 45 46.92,006682.00

The problem is when I'm looping through the string, the statement Mid(oCoord, i, 1) = "" {no space between the quotes} doesn't work at all while adding a single space between the quotes returns double spaces instead of removing the unneeded character. I tried Mid(oCoord, i, 1) = Replace(oCoord, Mid(oCoord, i, 1), "") but as a result I get ALL spaces removed.

PS: TRIM() function didn't help either!

Code:
        For i = 1 To Len(oCoord)
            sChar = Mid(oCoord, i, 1)
            Select Case True
            Case sChar = Chr(176)   ' degree sign " ° "
                Mid(oCoord, i, 1) = " "
            Case sChar = Chr(39)    ' single quote sign " ' "
                Mid(oCoord, i, 1) = " "
            Case sChar = Chr(34)    ' double quotes " "
                Mid(oCoord, i, 1) = " "
            Case sChar = Chr(43)    ' plus sign " + "
                Mid(oCoord, i, 1) = " "
            Case sChar = "N"
                Mid(oCoord, i, 1) = " "
            Case sChar = "E"
                Mid(oCoord, i, 1) = " "
            Case sChar = "S"
                Mid(oCoord, i, 1) = "-"
            Case sChar = "W"
                Mid(oCoord, i, 1) = "-"
            Case Else
            End Select
        Next

Can I get your assistance in this matter? Thank you very much in advance!!!

Rustam
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi,
let Your, first String be in A1, then try using a code of the form:
Code:
Sub Replacce()
Dim i&, a As String

a = Cells(1, 1).Value
For i = 33 To 224
   If Abs(i - 45) <> 1 And Not Abs(i - 52.5) <= 4.5 Then
      a = Replace(a, Chr(i), "")
    End If
Next i

Cells(1, 2).Value = a
End Sub
Best regards.
 
Upvote 0
Or you could use a UDF:

Code:
Function simurq(sInp As String) As String
    Const sRep As String = "NESW'°+"""
    Dim i As Long
    
    simurq = sInp
    For i = 1 To Len(sRep)
        simurq = Replace(simurq, Mid(sRep, i, 1), "")
    Next i
End Function

Code:
      --------------------A--------------------- -------B------- C
  1   N37° 8' 30.52",W107° 45' 46.92",+006682.00                  
  2   37 8 30.52,107 45 46.92,006682.00          A2: =simurq(A1)
 
Upvote 0
Thank you, SHG, that worked like a charm! :beerchug:

The most important lesson learned for me was looping through a string as an array: Const sRep As String = "NESW'°+"""

I've slightly modified your code though to account for S (South) and W (West) coordinates in which case instead of simply removing "S" or "W", the code now replaces these two chars with "-" (minus) sign.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,996
Messages
6,175,867
Members
452,678
Latest member
will_simmo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top