Excel – How find & replace using wildcards with regex substitution

microsoft excelregex

Is there a way to use regex substitution with Excel find & replace (in the GUI)?

Using a JavaScript example, I want to do something like this:
"John Smith".replace(/(.*)\s(\.*)/, "$2, $1") // returns "Smith, John"

Best Answer

There is an add-on at www.codedawn.com that adds regexp functionality to MS Excel's find&replace dialog. That's the answer! Just one note: replacements made by this add-on are NOT UNDOABLE. You will be warned to save your workbook before committing replace.

Related Solutions

Excel – Does Excel have Find & Replace match placeholder

Excel's pattern matching capabilities are extremely limited: literal characters, * to match any number of unspecified characters, and ? to match a single unspecified character. There is no native match placeholder capability, either in worksheet functions or in VBA.

However, as Excellll noted, you can use regex in VBA, and can develop user-defined functions that are regex-based.

To access regex in your code, add a reference to the "Microsoft VBScript Regular Expression 5.5" library via the Tools->References selections in the main menu for the Visual Basic code editor.

I have not found any comprehensive documentation for the library, but you can find a lot of bits and pieces - and example code - with a Google search.

Vba – How to Replace Words With VBScript Regex and VBA

Sub test()

    Dim RE As Object
    Set RE = CreateObject("VBScript.RegExp")

    RE.ignoreCase = True
    RE.Global = True

    RE.Pattern = "\b[a-z]+\b"
    Debug.Print RE.Replace("Findings,Actions", "xyz")

End Sub

Output

xyz,xyz

Best Answer

Related Solutions

Excel – Does Excel have Find & Replace match placeholder

Vba – How to Replace Words With VBScript Regex and VBA

Output

Related Question