How to use Regex Tools

Part of Ultimate Suite for Excel

A regular expression, also called a regex, is a method for matching text with patterns. A regex is created with specific syntax. If you are new to this topic, first refer to the following Microsoft guides: .NET regular expressions and Regular Expression Language - Quick Reference

With the Regex Tools add-in, you can use regular expressions in Excel to identify cells that match a certain condition or to extract, remove, or replace strings that match a regular expression.

• Before you start
• How to use Regex Tools
  • Match
  • Extract
  • Remove
  • Replace

Before you start

Before getting started, take the following points into consideration:

• The tool can process only one column at a time.
• The result will be pasted into a new column to the right of the selected one.
• By default, regular expressions are case-sensitive. You can use the (?i) pattern to make the matching case-insensitive.
• The Extract and Remove options recognize capture groups. A capture group is a part of a pattern that is enclosed in parentheses. If there are no capture groups in the regular expression, the add-in works with the whole match.
• You can Insert the result as a formula that is created with a custom Ablebits function. Such formulas will work only on machines with installed Ultimate Suite for Excel. If the workbook is opened on another computer where Ultimate Suite is not installed, the result will be lost.

How to use Regex Tools

To start, on the Ablebits Data tab, find the Text group and click on the Regex Tools icon:

On the left, you'll see the Regex Tools pane:

1. Select a column with the data to process. To get the entire table selected automatically, you can pick any cell in the table and click the Expand selection icon on the right.
2. Enter the regular expression for the tool to match. To learn more about the regular expression language, refer to the following Microsoft guides: .NET regular expressions and Regular Expression Language - Quick Reference.

   For example, on the screenshot above, we have a range with cells some of which contain telephone numbers that we need to extract. For the telephone number pattern "nnn-nnn-nnnn", the regular expression will be the following:
   (\d{3}-\d{3}-\d{4})

3. Here are the options available. Select any you need. Refer to the sections below for more details about the options.
4. You can Insert as a formula so the result will be updated each time the initial data changes. The formulas will be created with the help of the Ablebits custom functions.
5. The tool will insert a new column into your worksheet. Select Back up this worksheet to create a safe copy of your data.

Match

If you tick Match and then click the Match button, the tool will check whether each cell in the column contains text that matches the expression you entered into the Regex box. TRUE means that it does, FALSE—that it doesn't:

If you select the Insert as a formula option, the result will be created with the custom AblebitsRegexMatch function.

Extract

Tick Extract to get the string that matches the regular expression extracted:

If you select the Insert as a formula option, the result will be created with the custom AblebitsRegexExtract function.

Remove

Select Remove to find and delete text that matches the regular expression:

If you select the Insert as a formula option, the result will be created with the custom AblebitsRegexRemove function.

Replace

Pick Replace and enter the value that should appear instead of the string that matches the regular expression:

If you select the Insert as a formula option, the result will be created with the custom AblebitsRegexReplace function.