Add space after comma using VBA regex

Question

I am trying to use a regular expression to search for cells in a semicolon range, but there is no space after this comma. Then I just want to add a space between the comma and the next character. For example, a cell has Wayne,Brucetext inside, but I want to turn it on 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 away from Wayne Bruce, I get Wayn, ruce. How to save letters, but separate them?

Answer 1

Change the code as follows:

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

There will be a way out Bruce, Wayne.

, VBScript, .

look-ahead, .

, ([a-zA-Z]) $1. , .

(EDIT) REGEX

• ([a-zA-Z]) - , ,
• , - , ( , )
• (?=[a-zA-Z]) - , ( ), .

Answer 2

+ + + + , :

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

Answer 3

RegExp, stribizhev,

• RegExp , .
• varinat ,

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

Regex,

(any Alphabet),(any Alphabet)

,_ 

_ .

So, if you have Wayne,Bruce, then the pattern matches 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"

.