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.

Responses

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.

Reply

Hello Bill,

Thank you for your comment. The functionality you need is not available in the current version of the add-in, but our developers will look into the possibility to implement it. Stay tuned 😉

Reply
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

Reply

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.

Reply

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

Pete,

Unfortunately, with the current version of Regex Tools you won't get the result you need. However, our developers are going to improve the add-in functionality in one of the future releases of Ultimate Suite. As a workaround, we can suggest using the Extract Text and Add-Text tools at the moment.

In case you have any other questions or need further assistance, feel free to email us at support@ablebits.com.

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.