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.