Part of Ultimate Suite for Excel

How to use Regex Tools

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, 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:

Run Regex Tools.

On the left, you'll see the Regex Tools pane:
Regex Tools for Excel 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:
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.

Responses

Bill OLeary says:
February 5, 2022 at 1:57 pm

Thanks for asking this question Pete!
I was looking to evaluate this tool, but my immedite application needs to be able to
use back references in the regex replace, so I will wait unitl the future release.

Peter R Myran says:
October 30, 2021 at 2:05 pm

Hi Folks - new to your tools. Very happy to have RegEx!! If I wanted to reference a group in a replace operation, how would I write it? So using your phone number example ... say I wanted to replace a whole phone number with just the area code: my pattern: "(\d{3})-(\d{3})-(\d{4})" And my replace string would look like: "Area Code: $1" or maybe "Area Code: \1". But I get the whole match as if I wrote $0. How should I write it to reference buffer 1 ??

Thanks -

Pete

Katerina Bespalaya (Ablebits Team) says:
October 31, 2021 at 7:35 pm

Hi Peter,

Thank you for your feedback.

Please try to remove extra parentheses from your pattern so it will look like (\d{3}-\d{3}-\d{4}) and check if you get the needed result. If not, then please send us a screenshot showing the add-in pane with the expression you use and the options you specify, the column with the data you want to replace, the result you get and the result you expect to get. You can email it to support@ablebits.com. We'll be happy to help you further.

Peter Myran says:
November 1, 2021 at 2:49 pm

Thanks for your response. I will send you a screen capture, but first tell me how to express the capture group. IOW I am interested in extracting the first 3 digits as the area code. So the phone# text: 989-555-1234 wold be replaced with Area Code: 989 In other Regex editors and code I would write the phone patter as: (\d{3})[\s.-]?(\d{3})[\s.-]?(\d{4})
and the replace text as: Area Code: $1
How do I express the 1st capture group? $1 does not work and \1 doesn't either

Thanks!

Pete

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.