Does Excel have Find & Replace match placeholder?
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?
Solution 1:
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.
Solution 2:
Though this is a very late answer, I have had success with an add-in called RegEx Find/Replace to do exactly what you want. But I do not know of any built-in capability for this in Excel.
The add-in creates a button on the ribbon which launches a dialog box that looks very much like the Excel Find/Replace. Most of it works the same as well - you can search within a sheet or workbook, by columns or rows, match case, etc. (There is no format matching, though.)
Note, however, that the add-in uses regular expressions as implemented in VBScript and so works differently than the standard Excel Find/Replace. For example, your search string _* would not work. It matches any cell with zero or more underscores, which is anything. For your example, I had success finding _(\d+) and replacing with '($1). (The apostrophe is just to prevent Excel from interpreting (4) as a negative value.)
I am in no way affiliated with the website or developer - just found it through a web search. Also, I use Excel 2007 and cannot say whether this still works in later versions. I have used it successfully in both Windows 7 and Windows 10.
The RegEx Find/Replace add-in can be found at http://www.codedawn.com/excel-add-ins.php