8

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?

2
  • 3
    I don't believe so; however, you can use regex in VBA. It's not nearly as easy as a Search and Replace dialog, but it will work if you need to use regex.
    – Excellll
    Aug 11, 2013 at 20:20
  • 1
    As a workaround you could use LibreOffice which does support regular expressions including capture groups in Search/Replace. Here is a list of all supported constructs. It includes () in search strings to capture a part of the match and $1 in replacement strings to use it. Aug 11, 2016 at 9:48

4 Answers 4

8

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.

3
3

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

4
  • 1
    Could you edit your answer to include a short example of how to use the software you recommend?
    – Burgi
    May 31, 2016 at 15:43
  • 1
    Confirmed: I have used this and it works.
    – CAD bloke
    Sep 22, 2017 at 1:35
  • Warning: this add-on doesn't appear to register its replacements in the undo history.
    – Ruslan
    Oct 21, 2018 at 16:23
  • I can confirm the warning from @Ruslan. Changes are not available for undo, and in fact the undo stack is cleared. However, the add-in does prompt to save the file before making changes.
    – gspontak
    Jan 25, 2019 at 18:34
1

Rename the extension of the file from .xlsx to .zip
Unzip the whole lot
Locate the sheets in the subfolder \xl\worksheets
Open the file in emacs (or any other txt editor with sufficient power)
Change your stuff
Rezip, rename from .zip to .xlsx
Note: Excel keeps an internal list of computational dependencies, so if your changes involve references to other cells in some kind, recomputation will not happen. I solved this by a two step approach, the second step using excel's replace function which will trigger a correct internal update.
Sounds complicated but is actually a 5 min procedure!

1
  • I used this technique and it worked well. You need to be comfortable editing XML with a text editor, and also working with zip files. But if you're determined to use regex, this may describe you anyway. Quick tip: you can force recalculate the entire workbook with Ctrl+Alt+F9.
    – Mike Clark
    Mar 4, 2021 at 16:23
0

Another solution is to copy the sheet to a table in Word, do the find and replace, then copy it back to Excel.

1
  • 4
    This would be a better answer if you described how to do what you suggest.  How would you change _357 to (357)?  (I realize that the other answers to this question are also lacking in specific instructions, and they could all be improved.  I invite you to rise to a higher standard of quality.) Nov 7, 2019 at 22:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .