Add a space after comma using VBA regex

Question

I'm trying to use a regex to find cells in a range that have a comma, but no space after that comma. Then, I want to simply add a space between the comma and the next character. For example, a cell has Wayne,Bruce text inside, but I want to turn it to Wayne, Bruce.

I have a regex pattern that can find cells with characters and commas without spaces, but when I replace this, it cuts off some characters.

Private Sub simpleRegexSearch()
    ' adapted from http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
    Dim strPattern As String: strPattern = "[a-zA-Z]\,[a-zA-Z]"
    Dim strReplace As String: strReplace = ", "
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("P1:P5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.TEST(strInput) Then
                Debug.Print (regEx.Replace(strInput, strReplace))
            Else
                Debug.Print ("No Regex Not matched in " & cell.address)
            End If
        End If
    Next

    Set regEx = Nothing
End Sub

If I run that against "Wayne,Bruce" I get "Wayn, ruce". How do I keep the letters, but separate them?

Answer 1

Change the code the following way:

Dim strPattern As String: strPattern = "([a-zA-Z]),(?=[a-zA-Z])"
Dim strReplace As String: strReplace = "$1, "

Output will be Bruce, Wayne.

The problem is that you cannot use a look-behind in VBScript, so we need a workaround in the form of a capturing group for the letter before the comma.

For the letter after the comma, we can use a look-ahead, it is available in this regex flavor.

So, we just capture with ([a-zA-Z]) and restore it in the replacing call with a back-reference $1. Look-ahead does not consume characters, so we are covered.

(EDIT) REGEX EXPLANATION

• ([a-zA-Z]) - A captured group that includes a character class matching just 1 English character
• , - Matching a literal , (you actually do not have to escape it as it is not a special character)
• (?=[a-zA-Z]) - A positive look-ahead that only checks (does not match, or consume) if the immediate character following the comma is and English letter.

Answer 2

If we replace all commas with comma+space and then replace comma+space+space with comma+space, we can meet your requirement:

Sub NoRegex()
   Dim r As Range
   Set r = Range("P1:P5")
   r.Replace What:=",", Replacement:=", "
   r.Replace What:=",  ", Replacement:=", "
End Sub

Answer 3

Uses the same RegExp as in the solution from stribizhev but with two optimisations for speed

1. Your current code sets the RegExp details for every cell tested, these only need setting once.
2. Looping through a varinat array is much faster than a cell range

code

Private Sub simpleRegexSearch()
    ' adapted from http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops
    Dim strPattern As String:
    Dim strReplace As String:
    Dim regEx As Object
    Dim strInput As String
    Dim X, X1
    Dim lngnct

    Set regEx = CreateObject("vbscript.regexp")

    strPattern = "([a-zA-Z])\,(?=[a-zA-Z])"
    strReplace = "$1, "

    With regEx
           .Global = True
           .MultiLine = True
           .IgnoreCase = False
           .Pattern = strPattern

    X = ActiveSheet.Range("P1:P5").Value2

    For X1 = 1 To UBound(X)
            If .TEST(X(X1, 1)) Then
                Debug.Print .Replace(X(X1, 1), strReplace)
            Else
                Debug.Print ("No Regex Not matched in " & [p1].Offset(X1 - 1).Address(0, 0))
            End If
    Next
    End With

    Set regEx = Nothing
End Sub

Answer 4

What you are doing via Regex is to find a pattern

(any Alphabet),(any Alphabet)

and then replace such pattern to

,_ 

where _ implies a space.

So if you have Wayne,Bruce then the pattern matches where e,B. Therefore the result becomes Wayn, ruce.

Try

Dim strPattern As String: strPattern = "([a-zA-Z]),([a-zA-Z])"
Dim strReplace As String: strReplace = "$1, $2"

.