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, Aug 02 '21 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, Aug 02 '21 at 13:42
My apologies, I made an error while stating the requirements in the `xxx` part. I have edited it now. — Bhavesh Shaha, Aug 02 '21 at 13:49

score 2 · Accepted Answer · answered Aug 02 '21 at 13:43

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 02 '21 at 13:43

GSerg

76,472
17
159
346

Apologies if you've seen my previous comment (deleted because I haven't read the question properly). Nice answer +1 – Cristian Buse Sep 02 '21 at 13:02
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. – Cristian Buse Sep 02 '21 at 13:52
@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. – GSerg Sep 02 '21 at 14:42
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](https://gist.github.com/cristianbuse/4eb1ca10710205e8fa175fdb7cf3a8fe) 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 Sep 02 '21 at 15:02
Yeah, just realized that your one would fail too because of the same reason. – Cristian Buse Sep 02 '21 at 15:09
@CristianBuse A string always has two bytes per character. The result of `StrConv(s, vbFromUnicode)` is a byte array, not a string, and it is likely that the data has been corrupted. Converting to and from "double unicode" is [not good for you](https://stackoverflow.com/a/14292880/11683). – GSerg Sep 02 '21 at 15:27
Thanks for the link. Also, I did not know that ```StrConv(s, vbFromUnicode)``` returns an array. So you can understand my confusion. Will definitely need to play around a bit to fully understand it as I never used it before (was just aware of it). Ok then, so the solution linked in my comment above is viable (every second byte would need an extra check though). Good to know. – Cristian Buse Sep 02 '21 at 15:40

Pᴇʜ · Answer 2 · 2021-08-02T13:53:45.613

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!

Replace string in VBA

2 Answers2