Add one space after double quotations “, If double quotations are not followed by (Space , “x” , “X” , or “*”)

Question

I need to add one space after double quotations “, If double quotations are not followed by (Space , “x” , “X” , or “*”).
My provided code only works correctly with the first example.

Function Add_Space(str As String) As String

   Static re As New RegExp
   
    re.Pattern = "("")"
    
     re.Global = True
      re.IgnoreCase = True
        Add_Space = re.Replace(str, "$1 ")
            
End Function

Current	Expected Result
Repair of 8"sales	Repair of 8" sales
Repair of 8"x12"sales	Repair of 8"x12" sales
Repair of 8"*12" sales	Repair of 8"*12" sales
Repair of 8" sales	Repair of 8" sales

Answer 1

As I always struggle with RegEx, I tend to solve such tasks with the simple VBA functions Split and Join.

Split the input by the quote character, loop over all pieces, check if the piece starts with blank, "x" or "*" and if not, just add the space to the left. Then glue everything together again.

Function AdjustQuotes(s As String) As String
    Const Separator = """"
    Dim tokens() As String, i As Long
    tokens = Split(s, Separator)
    For i = 1 To UBound(tokens)
        Dim startChr As String
        startChr = LCase(Left(tokens(i), 1))
        If startChr <> " " And startChr <> "x" And startChr <> "*" Then
            tokens(i) = " " & tokens(i)
        End If
    Next i
    AdjustQuotes = Join(tokens, Separator)
End Function