Regular expressions in Excel | Find, extract and replace text

Regular expressions in Excel with !SEMTools

Many people have heard of regular expressions, but not everyone knows that Microsoft Excel supports them “under the hood.” Regular expressions allow you to work with text much faster, find complex patterns, and solve advanced analytical tasks.

A regular expression (regex) is a pattern that describes what the text you are looking for looks like: “four digits in a row”, “a word that starts with a capital letter”, “everything after the last comma”. Excel checks each cell against the pattern and finds, extracts or replaces the matching fragments.

Built-in regular expression functions exist only in Excel 365. Excel 2010–2021 doesn’t have them, and until recently the only way to use regex in these versions was to write your own VBA functions.

With the !SEMTools add-in, you don’t need VBA at all. Regular expression functions work in any version of Excel starting with 2010 and are available in the free version of the add-in with no time limit.

A basic understanding of regex syntax is enough to solve tasks that are nearly impossible with standard Excel functions, or that would require extremely long formulas. Examples of such “mega formulas” can be found here:

• Extract latin letters in Excel
• Finding numbers inside text

Functions available with regular expressions

With !SEMTools installed, you can use three regular expression functions directly in Excel: REGEXMATCH, REGEXEXTRACT, and REGEXREPLACE.

Their syntax and behavior match Google Sheets, so formulas will be almost fully compatible between the two. The only difference is described below, in the REGEXEXTRACT section.

REGEXMATCH

Returns TRUE or FALSE depending on whether the text matches a pattern.

=REGEXMATCH("text","pattern")

REGEXEXTRACT

Extracts the first piece of text matching the pattern. In Google Sheets, if no match is found, it returns an error. In !SEMTools, it returns a blank cell instead.

=REGEXEXTRACT("text","pattern")

REGEXREPLACE

Replaces all matches of the pattern with the specified text.

=REGEXREPLACE("text","pattern","replacement")

Regular expressions in Excel 365

Excel 365 now has its own regular expression functions. The patterns from this article work in them too, but the function names and arguments differ:

If you use Excel 2010–2021, or share files with colleagues who don’t have Excel 365, the !SEMTools functions remain the only way to use regex without VBA. Keep in mind that they work only where !SEMTools is installed, so convert formulas to values before sending the file to someone else.

Regular expression syntax cheat sheet

Before moving on to the examples, here are all the symbols used below in one table. You don’t need to memorize them: come back to this table whenever you build your own pattern.

Note that \w also matches the underscore and digits, not just letters. If you need letters only, use [A-Za-z].

Real-life examples of using regular expressions

Below are practical examples, from simple to more advanced, to show the real power of regular expressions in Excel. To let regular Excel users get the most out of regex, !SEMTools also includes a set of quick procedures, so most of the examples can be done without typing a single formula.

Extracting data from cells with regex

Extracting content before or after the first number

.*?\d
\d.*

Two simple expressions. The dot means any character, and “*” is a quantifier meaning “zero or more repetitions”. By default, quantifiers are greedy: they capture as many characters as possible. A question mark after “*” makes the quantifier lazy, so the expression stops at the very first digit it meets.

The difference is easy to see on the string “Item A-1024, batch 7 from 2023”:

• .*?\d returns “Item A-1” — the text up to the first digit;
• .*\d returns “Item A-1024, batch 7 from 2023” — the text up to the last digit;
• \d.* returns “1024, batch 7 from 2023” — everything starting from the first digit.

Here “\d” means “digit”. Since there is no quantifier after “\d” in the examples above, it matches exactly one digit. If you need to drop that digit from the result, you can do it afterwards: !SEMTools has simple ways to remove characters at the beginning or end of a cell.

Digits can also be expressed with another pattern:

[0-9]

Extracting all digits

How do you extract numbers from text? The regular expression for this is incredibly simple:

\d

Depending on the extraction mode, the result will be either the first digit or all digits in the cell.

If you want to output whole numbers separated by a delimiter rather than one continuous string of digits, use the “+” quantifier (one or more repetitions) and specify the delimiter when extracting:

\d+
[0-9]+

The same approach works for any other characters, not just digits.

Extracting only 4-digit numbers (years)

In the previous example, numbers like “1” or “7” are extracted along with the years. To extract only sequences of exactly four digits, change the expression. There are several options:

\d\d\d\d
[0-9][0-9][0-9][0-9]
\d{4}
[0-9]{4}

The last two use the curly-brace quantifier. It sets the exact number of repetitions of the pattern right before it — here, exactly four digits in a row. Curly braces have other forms too: {4,} means four or more, {2,4} means from two to four.

Extracting Latin letters

The pattern [a-zA-Z] matches all lowercase and uppercase English letters. The hyphen means “all characters between a and z” (and between A and Z) in the Unicode table. Square brackets work as “OR”: each element or range inside them is checked, and the expression finds nothing only if the string contains none of them.

Extracting characters at the start or end of a cell by condition

The standard RIGHT and LEFT functions extract the last and first N characters of a cell, but that’s where their abilities end. With regular expressions you can extract:

• The last sentence of a cell if it is an exclamation. Pattern: [A-Z][^.!?]*!$. It looks for a capital letter followed by any characters except sentence-ending marks, with “!” at the very end of the cell. From “Hi there. How are you!” it extracts “How are you!”, and from “We are in New York!” — the whole sentence, despite the capital letters in the middle.
• Leading characters from a given set, if the cell starts with them. For example, ^[A-Z]+ extracts a product code prefix: from “SKU-100 phone case” you get “SKU”. If the cell doesn’t start with capital letters, the result is empty.
• Likewise, trailing characters from a set, if the cell ends with them. For example, \d{1,3}$ extracts one to three digits at the end of a cell: from “Case for iPhone 15” you get “15”.

Checking cells against a regular expression

If you don’t need to extract anything and only want to check whether the data matches a pattern (for example, to filter it afterwards), use the procedure equivalent to the REGEXMATCH formula.

Finding numbers with exactly N digits

The pattern depends on whether N digits is a required or a sufficient condition, i.e. whether sequences of N+1, N+2 and more digits should also count. If they should, the pattern is the same as above:

\d\d\d\d
[0-9][0-9][0-9][0-9]
\d{4}
[0-9]{4}

If you need strictly N digits and no more, the easiest way is the word boundary \b:

\b\d{4}\b

This pattern finds “2023” in “year 2023” and “2023-24”, but ignores “20231”. One caveat: if the digits are glued to letters, as in “abc1234”, the word boundary won’t work. For \b, letters and digits are equally “word” characters, so there is no boundary between them.

In such cases, solve the task in two passes:

• in the first pass, extract the digits together with the start/end of the text or the non-digit characters around them (this checks that there are no other digits next to them);
• in the second pass, extract the digits themselves.

Patterns for the first pass:

(^|\D)\d\d\d\d($|\D)
(^|\D)[0-9][0-9][0-9][0-9]($|\D)
(^|\D)\d{4}($|\D)
(^|\D)[0-9]{4}($|\D)

For the second pass, simply extract \d{4} from the fragments you got.

Looking at the syntax, you can see what each symbol means:

• the vertical bar “|” means “OR”;
• parentheses “( )” group the alternatives and separate them from the rest of the expression;
• the caret “^” means the start of the text, and the dollar sign “$” means the end;
• \D means non-digit characters. Note that the uppercase letter reverses the meaning of \d. The same applies to \w / \W (letters, digits and underscore vs. everything else) and \s / \S (whitespace vs. non-whitespace).

Checking if a cell starts with a number

=REGEXMATCH(A1,"^\d.*")

“^” means start of the cell, “\d” means a digit, “.*” means any characters after that. Instead of the formula, you can also run the check procedure on a copy of the source range without typing anything.

Finding repeated blocks and mirror numbers (backreferences)

This section exists thanks to a reader’s question: how do you find “mirror” numbers like 4915491 in a list? First, a clarification. 4915491 isn’t actually a mirror number: read right to left, it is 1945194. It has the same block “491” at both ends with one digit in between. A true mirror number (palindrome) would be 4915194. The patterns for these two cases are different, so let’s look at both.

Both solutions rely on backreferences. Parentheses don’t just group part of the expression — they also remember what was matched, and \1, \2 and so on require exactly the same text to appear again later in the string.

A repeated block at both ends, as in 4915491. For seven-digit numbers of the form “3 digits + 1 + the same 3”:

=REGEXMATCH(A1,"^(\d{3})\d\1$")

• ^ and $ — start and end of the cell;
• (\d{3}) — three digits saved to a group;
• \d — one digit in the middle;
• \1 — a reference to the group, i.e. exactly the same three digits again.

A true mirror (palindrome), as in 4915194. For numbers of a given length, the pattern uses the groups in reverse order. For example, for seven-digit numbers:

=REGEXMATCH(A1,"^(\d)(\d)(\d)\d\3\2\1$")

The first digit must match the last one, the second must match the second-to-last, and so on.

Replacing text by regex pattern

The most common case is replacing with nothing, when the goal is simply to delete certain characters from the text. The most popular ones:

• Remove all numbers from text — pattern \d
• Remove punctuation — pattern [.,!?;:"()]
• Remove all characters except letters and numbers — pattern [^A-Za-z0-9 ]

For example, this formula removes everything except letters, digits and spaces:

=REGEXREPLACE(A1,"[^A-Za-z0-9 ]","")

Note that the shorter [^\w ] isn’t quite the same: \w also matches the underscore, so “_” would stay in the text.

Sometimes you need a real replacement, not a deletion — for example, replacing accented or special characters (umlauts, cedillas, letters with diacritics from European alphabets) with their English equivalents. This task is popular among SEO specialists who build clean URLs for European websites from the original keywords.

Splitting text by characters or by letters/numbers

To split a cell into individual characters, extract all characters with a delimiter. The pattern is a plain dot, which means any character:

.

Strictly speaking, splitting “letters from digits” is a tricky task. But regex lets you separate digit sequences from non-digit ones. Pattern to split numbers from letters:

([^\d.]+|[\d.]+)

The dot is added to the digits on purpose, so decimal numbers like “2.5” aren’t broken into pieces. For more on this task, including formula solutions without regex, see how to separate text and numbers in Excel.

Inserting text after the first word

You can replace only the first match of a space with your chosen text. This is not possible in Excel’s standard Find and Replace, but is available in !SEMTools RegEx Replace.

Inserting a character before or after every word

The add-in solves this in two clicks with a ready-made procedure in the Change words menu, but a simple replacement pattern works too:

($| )
(^| )

The first pattern matches spaces or the end of the text, the second matches spaces or the start of the text. The vertical bar is the same “OR”.

Replace them with a space plus your character on the left or right, respectively. This adds an extra space at the start or end of the cell, so you’ll want to remove extra spaces or remove the last character afterwards.

Regex for whole words

When it comes to extracting specific words, regular expressions get incredibly complex. That’s why !SEMTools simplifies the task by applying regex patterns to words as separate entities.

Finding words by regular expression

The procedure effectively splits a cell into separate words and checks each of them against your pattern.

What patterns can you use? Almost anything: email addresses, ZIP codes, dates, product codes. You can look for professional terminology, job titles (-er, -or, -ist), linguistic patterns like nouns ending in “-tion” or “-ness”, adverbs ending in “-ly”, words with prefixes (un-, re-), abbreviations (LLC, IT, CRM) and special formats like #123/45.

A few ready-made patterns for such tasks:

Extracting words by regular expression

Example: extract words containing both letters and numbers.

([a-z]\d|\d[a-z])

Here, “|” means “or,” so the pattern matches either a letter followed by a digit or a digit followed by a letter. Note that the letter and digit must stand right next to each other. To also extract words like “asdf-13” or “1234-d”, allow any characters between them:

([a-z].*\d|\d.*[a-z])

If your data contains uppercase letters, replace [a-z] with [A-Za-z].

Deleting words by regular expression

Sometimes text needs to be cleaned of unwanted words that share a common pattern. Once you describe that pattern as a regular expression, you can easily delete such words from the text with !SEMTools.

As with finding, the procedure checks each word separately, so the patterns stay short:

• \d — deletes all words containing digits: “buy phone case iphone15 cheap 2024” becomes “buy phone case cheap”;
• ^.{1,2}$ — deletes short words of 1–2 characters, such as “a”, “to”, “in”;
• ^[A-Z]+$ — deletes words written entirely in capitals, such as SKU codes, but keeps “iPhone” or “Excel”;
• [@/] — deletes words containing “@” or “/”, i.e. emails and URL fragments.

You can do the same with a formula, but then the pattern has to describe the whole string at once. For example, deleting words with digits:

=REGEXREPLACE(A1,"\S*\d\S*","")

\S* means any non-whitespace characters before and after the digit, i.e. the whole word. After such a replacement, double spaces will remain in the text, so trim the extra spaces afterwards.

Cleaning cells that don’t match a pattern

When you have a dataset with errors you have no time to deal with, and you only need data that is 100% valid, you can use regular expressions to remove any cell content that doesn’t meet your pattern requirements.

Examples of tasks and patterns:

Note that every pattern contains ^ and $. Without them, any cell where the fragment appears anywhere would pass the check. For example, \d+ matches both “12345” and “Item 7”.

Common regular expression mistakes

If a pattern finds the wrong thing or nothing at all, 99% of the time the reason is one of these:

• Forgot ^ and $. The pattern \d{4} finds four digits in “12345” too, because it looks for a fragment, not the whole cell. To check the entire cell, use ^\d{4}$.
• Didn’t escape a special character. The dot, plus, parentheses and question mark are special symbols in regex. To search for an actual dot in a domain or a plus in a phone number, put a backslash before them: \., \+, \(.
• Expected \w to match letters only. \w also matches digits and the underscore, and doesn’t match accented letters like “é”. For English letters only, use [A-Za-z].
• A greedy quantifier captured too much. .* and .+ take as many characters as possible. To stop at the first match, add “?”: .*?.
• A colleague sees the #NAME? error. REGEXMATCH, REGEXEXTRACT and REGEXREPLACE work only where !SEMTools is installed. Replace formulas with values before sending the file.

See also:

• Delete words containing Latin characters
• REPLACE function

Summary: where to start with regex in Excel

I hope you found what you were looking for on this page, and regular expressions no longer look like a random set of symbols. Start with the simple patterns from the cheat sheet: \d, [A-Za-z], ^ and $ solve most everyday tasks. More complex expressions will gradually build themselves out of these bricks.

If your task is a typical one, a ready-made tool is often faster than writing a pattern: extract characters by type or position, delete words by condition, or find cells by several criteria.