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

Before getting started, please take the following points into consideration:

  • The tool can process only one column at a time.
  • The result will be pasted to 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. Please note, 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, in the Ablebits Data tab, find the Text group and click on the Regex Tools icon:

Run Regex Tools.

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

  1. Select a column with 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. For the regular expression language, refer to the guide.

    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. These are the options, select one of them according to your task. Refer to the sections below for more details.
  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. As the tool inserts a new column with result to your original sheet, you can tick the Back up this worksheet box to create a copy of the worksheet in its initial state.

Match

If you tick Match and then click the Match button, the tool will identify if each cell in the column contains text that matches the expression you entered into the Regex box. TRUE means that it does, FALSE—that not:
The Match result.

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:
The Extract result.

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:
The Remove result.

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:

The Replace result.

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