Find a string by VBA Regular Expression and replace a part of that string

**taps** · 03-25-2013, 06:18 AM

Hi All,

I would like to find ; (semicolon) which does not contain double quote on both side of it in an excel range (say- A1 to A10).
Then all [any character] ; [any character] should replace with [that character] - [that character].

Examples:

a;b ---> a-b
a; b ---> a- b
a ;b ---> a -b
a12c;d25dd ---> a12c-d25dd
a123;425dd ---> a123-425dd
a123";"325dd ---> a123";"325dd (No change)
a123;"325dd ---> a123;"325dd (No change)
a123";325dd ---> a123";325dd (No change)

Thanks in advance for your support.

Regards
taps

**hafizimran** · 03-25-2013, 08:45 AM

Why to use just RegEx? another method can't be used?

**hafizimran** · 03-25-2013, 09:17 AM

Sub ReplaceWithRegEx()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Pattern = "[^""] *(;) *[^""]"
        For i = 1 To 10
        If .test(Cells(i, 1)) Then
            Debug.Print Cells(i, 1).Address
            Cells(i, 1) = Replace(Cells(i, 1), ";", "-")
        End If
        Next
    End With
End Sub

**JosephP** · 03-25-2013, 09:38 AM

perhaps

Sub replaceText()
   With Range("A1:A10")
      .Replace """;""", "|^|", xlPart
      .Replace """;", "|^", xlPart
      .Replace ";""", "^|", xlPart
      .Replace ";", "-", xlPart
      .Replace "|^|", """;""", xlPart
      .Replace "|^", """;", xlPart
      .Replace "^|", ";""", xlPart
   End With
End Sub

**jindon** · 03-25-2013, 09:55 AM

Should be

Sub test()
    Dim r As Range
    With CreateObject("VbScript.RegExp")
        .Global = True
        .Pattern = "([^""]);(?!"")"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            r.Value = .Replace(r.Value, "$1-")
        Next
    End With
End Sub