Replace string in VBA

Question

sampleString = "Lorem ipsumxolor sit"

I want the immediate left and right characters of x to be blank. The desired output would be "Lorem ipsu x lor sit"

Using stringtext = replace(stringtext, "x", " x "), the output is Lorem ipsum x olor sit. However, the length of the string obviously increases and doesn't match the desired output.

Another limitation is that incase of sampleString = "Lorem ipsumxxxolor sit". I can't use stringtext = replace(stringtext, "x", " x ") as the output becomes Lorem ipsum x x x olor sit instead of the desired result Lorem ipsu xxx lor sit. I can use replace(stringtext, "xxx", " xxx ") but that would cause me to use multiple conditions instead of one single solution.

Is there an efficient way to deal with this?

Thank you!

if you add 2 more spaces I think it makes sense that string length is changed. isnt it? — balderman
– balderman, Commented Aug 2, 2021 at 13:32
I think your requirements are unclear. For the first example, you write the desired result would be "Lorem ipsu x lor sit" - replacing the character left and right of the x with a blank (removing "m" and "o"). For the second example, you write the desired result is "orem ipsum xxx olor sit amet", which insert additional spaces but leave the "m" and the "o" in place. — FunThomas
– FunThomas, Commented Aug 2, 2021 at 13:42
My apologies, I made an error while stating the requirements in the xxx part. I have edited it now. — Bhavesh Shaha
– Bhavesh Shaha, Commented Aug 2, 2021 at 13:49

GSerg · Accepted Answer · 2021-08-02 13:43:29Z

2

efficient

Private Function SpaceOutExes(ByVal s As String) As String
  SpaceOutExes = s
  
  Dim i As Long
  Dim PrevCharIsX As Boolean
  
  PrevCharIsX = Left$(SpaceOutExes, 1) = "x"
  
  For i = 2 To Len(SpaceOutExes)
    If Mid$(SpaceOutExes, i, 1) = "x" Then
      If Not PrevCharIsX Then Mid$(SpaceOutExes, i - 1, 1) = " "
      PrevCharIsX = True
    Else
      If PrevCharIsX Then Mid$(SpaceOutExes, i, 1) = " "
      PrevCharIsX = False
    End If
  Next
End Function

Dim sampleString As String
sampleString = "Lorem ipsumxolor sit"
  
Debug.Print SpaceOutExes(sampleString)

answered Aug 2, 2021 at 13:43

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

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

7 Comments

Cristian Buse Over a year ago

Apologies if you've seen my previous comment (deleted because I haven't read the question properly). Nice answer +1

Cristian Buse Over a year ago

I would replace PrevCharIsX = Left$(SpaceOutExes, 1) = "x" with PrevCharIsX = True and start the loop from 1 instead of 2. I've tried your approach on a byte array (after converting the string to byte array) and it's about 3 times faster but it would assume that the string is always Unicode (or not) so I prefer your solution which works with characters rather than char codes.

GSerg Over a year ago

@CristianBuse With your proposed change a string that starts with two non-x in a row will incorrectly have the first character spaced out (SpaceOutExes("Lorem ipsumxolor sit") => " orem ipsu x lor sit" instead of the correct "Lorem ipsu x lor sit"). And strings are always Unicode in VBA.

Cristian Buse Over a year ago

My bad. I was testing with something starting with "x". I was trying to say that the string doesn't necessarily have 2 bytes per char as one could use StrConv(s,vbFromUnicode) and so looping through an array of bytes gets difficult when you don't know that the step should be 1 or 2 in the For loop. Here is an example with step 2. It is 3 times faster than your approach but not good because it would fail if I call with SpaceOutExes(StrConv("Lorem ipsumxolor sit",vbFromUnicode))

Cristian Buse Over a year ago

Yeah, just realized that your one would fail too because of the same reason.

|

Pᴇʜ · Accepted Answer · 2021-08-02 13:53:45Z

You need to cut the original string in pieces and put it together the way you like:

Option Explicit

Public Sub StringExample()
    Dim SampleString As String
    SampleString = "Lorem ipsumxolor sit"
    
    Dim FoundPosition As Long
    FoundPosition = InStr(SampleString, "x")
    
    Dim ResultString As String
    ResultString = Left$(SampleString, FoundPosition - 2) & " " & Mid$(SampleString, FoundPosition, 1) & " " & Mid$(SampleString, FoundPosition + 2)
End Sub

Output is then

Lorem ipsu x lor sit

Or for multiple x:

Public Sub StringExampleMulti()
    Const Delimiter As String = "x"
    
    Dim SampleString As String
    SampleString = "Lorem ipsumxxxolor sit amxet, conse!"

    Dim Splitted() As String
    Splitted = Split(SampleString, "x")
    
    Dim ResultString As String
    
    Dim i As Long
    For i = LBound(Splitted) To UBound(Splitted)
        If Splitted(i) <> vbNullString Then
            If i = LBound(Splitted) Then
                ResultString = Left$(Splitted(i), Len(Splitted(i)) - 1) & " " & Delimiter
            ElseIf i = UBound(Splitted) Then
                ResultString = ResultString & Delimiter & " " & Right$(Splitted(i), Len(Splitted(i)) - 1)
            Else
                ResultString = ResultString & " " & Mid$(Splitted(i), 2, Len(Splitted(i)) - 2) & " "
            End If
        Else
            ResultString = ResultString & Delimiter
        End If
    Next i
     
    Debug.Print ResultString
End Sub

outputs:

Lorem ipsu xxx lor sit a x t, conse!

Collectives™ on Stack Overflow

Replace string in VBA

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related