microsoft excel
I want to use wildcards in Excel Find & Replace dialog, to replace cells with a pattern match. Made up example column:
_4
_44
_13
...
I want to replace these values with:
(4)
(44)
(13)
...
So I tried with _* which finds the pattern succesfully, but I couldn't find anywhere if Excel features match placeholder. For example with regex I would have used brackets and \1 as match placeholder. Does Excel have this feature at all?
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.
This can be done without changing the original data layout or joining any data. You can use an Index/Match combo like this:
=IFERROR(INDEX(Sheet2!$C$1:$C$10,MATCH(Sheet1!C2+Sheet1!D2,INDEX(Sheet2!$A$1:$A$10+Sheet2!$B$1:$B$10,0),0)),"")
Best Answer
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->Referencesselections 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.