How do I replace space characters in VBA?

Question

I am trying to replace spaces with dashes (-). When I am going through my characters and every time I find a space between them I want to replace them with a -. For instance if I have a string: A B, it should be converted into A-B. Here is my code:

Sub prac()
      Dim x As String, a As Long, lastrow As Long, i As Long
      Dim xcell As String
      x = "-"
      a = 1
      lastrow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
         For i = a To lastrow
           xcell = Worksheets("Sheet1").Range("A" & i)
              If InStr(1, xcell, "") > 0 Then
           Worksheets("Sheet1").Range("A" & i) = strReplace("xcell", "", x)
             End If
         Next i
End Sub

If there are multiple spaces, do you want a dash for each space or a single dash? — Dan
– Dan, Commented Apr 17, 2015 at 14:33
@dan08 ya anything between characters so A B so if i have multiple space need to replace with - or i might have single space like A B and replace with one - — lisa_rao007
– lisa_rao007, Commented Apr 17, 2015 at 14:43

Sam · Accepted Answer · 2015-04-17 14:38:38Z

4

For replace a substring with another substring you should use the function Replace

MsgBox Replace("the string you want to manipulate", " ", "-")

the " " stands for the space, and "-" is you char to be substitute.

So your last line of code should be (if I understood correctly your code):

Worksheets("Sheet1").Range("A" & i) = strReplace(xcell, " ", x)

Good luck

answered Apr 17, 2015 at 14:38

Sam

3241 gold badge4 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Verzweifler · Accepted Answer · 2015-04-17 14:49:39Z

I see three and a half errors in your code:

Your Instr(1, xcell, "") is searching for an empty String. Try Instr(xcell, " ") to detect spaces (and then only the first of possibly many spaces).
strReplace("xcell", "", x): The method you're looking for is replace(), see https://msdn.microsoft.com/de-de/library/bt3szac5%28v=vs.90%29.aspx.
If you were to write replace("xcell", " ", x) (note the already corrected error #1), you wouldn't look inside the variable xcell, but inside the String "xcell". Leave away the quotation marks to address the variable.
The half error is, that you don't have to search for occurrences of " ". The command Worksheets("Sheet1").Range("A" & i) = replace(xcell, " ", x) will replace all spaces with dashes. If there are no spaces, nothing will be replaced.

JanTheGun · Accepted Answer · 2015-04-17 14:39:09Z

0

You just need to fix the "Replace" Function

Sub prac()
      Dim x As String, a As Long, lastrow As Long, i As Long
      Dim xcell As String
      x = "-"
      a = 1
      lastrow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
         For i = a To lastrow
           xcell = Worksheets("Sheet1").Range("A" & i)
              If InStr(1, xcell, "") > 0 Then
           Worksheets("Sheet1").Range("A" & i) = Replace(xcell, " ", x)
             End If
         Next i
End Sub

The function name was wrong. Also you used the string "xcell" instead of the variable xcell and the second parameter must be a blank space.

answered Apr 17, 2015 at 14:39

JanTheGun

2,22318 silver badges25 bronze badges

Collectives™ on Stack Overflow

How do I replace space characters in VBA?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related