Regex Tools available in Text Toolkit for Excel will help you track down strings that match any regular expression you enter. Thus, you can get these strings easily extracted, removed, or replaced.
First of all, open the add-in pane by clicking the Text Toolkit icon on your Excel ribbon:
Then click on Regex Tools:
Select a column with the strings you would like to look for and decide on the options you're interested in.
- Here you can see the selected range.
- Enter a regular expression.
You can find previously entered regular expressions in the drop-down list.
- On the right side of the Regex field, you can see a question mark. Click it if you have difficulty in creating regular expressions. You'll be redirected to regex101.com for reference.
- If you want the add-in to return "TRUE" in the case of matching strings, select Match. If matches are missing, "FALSE" will be returned. Both Boolean values are to appear in an additional column that will be inserted on the right of the column that is going to be processed.
- Opt for Extract to get an additional column with retrieved matches.
- Choose Remove to let the add-in return the data cleared of matching strings and put it into an additional column.
- To change all the matches in bulk, select Replace with and type a substitute string. An additional column with updated records will appear.
- If letter case is important to you, don't forget to check the Case-sensitive option.
- A check mark next to Enable Undo adds the Undo button, thus making it possible for you to get back to the previous state of your worksheet with the click of a mouse.
To process your data, click the final action button whose label changes according to the selected operation (Match/Extract/Remove/Replace).
