ExcelRoadMap.com

Common Regular Expressions for Excel Users

Version: Microsoft 365
Last update:
Tip: Use ⬆️ ⬇️ to turn the page up and down. We recommend browsing with a large screen for a better reading experience.

1. Common Regular Expressions

Here is a series of commonly used regular expressions:
EmailsPhone NumbersSocial Security NumbersPricesNumbersDatesTimes

Theses regular expressions are used in REGEXEXTRACT function.
Common Regular Expressions
Extract Emails
Text:
Regular Expression:
[email protected][a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}
1john.doe[a-zA-Z0-9._%+-]+
2@@
3company[a-zA-Z0-9.-]+
4.\.
5com[a-zA-Z]{2,}
1[a-zA-Z0-9._%+-]+
[a-zA-Z0-9._%+-] Character set
a to z,   A to Z,   0 to 9,   character .,   character _,   character %,   character +,   character -.
+ Quantifier
match 1 or more of the preceding token
2@ Character
matches a "@" character
3[a-zA-Z0-9.-]+
[a-zA-Z0-9.-] Character set
a to z,   A to Z,   0 to 9,   character .,   character -.
+ Quantifier
match 1 or more of the preceding token
4\. Character
matches a "." character
5[a-zA-Z]{2,}
[a-zA-Z] Character set
a to z,   A to Z.
{2,} Quantifier
match 2 or more of the preceding token

4. Extract Phone Numbers

Extract Phone Numbers using Regular Expression.
Extract Phone Numbers

5. REGEX for Phone Numbers

The Regular Expression for Phone Numbers is \(\d{3}\)\s?\d{3}[-\s]?\d{4}
Text:
Regular Expression:
(123) 456-7890\(\d{3}\)\s?\d{3}[-\s]?\d{4}
1(\(
2123\d{3}
3)\)
4 \s?
5456\d{3}
6-[-\s]?
77890\d{4}
1\( Character
matches a "(" character
2\d{3}
\d Digit
matches any digit character (0-9)
{3} Quantifier
match 3 of the preceding token
3\) Character
matches a ")" character
4\s?
\s Character set
any whitespace character (spaces, tabs, line breaks)
? Quantifier
match between 0 and 1 of the preceding token.
5\d{3}
\d Digit
matches any digit character (0-9)
{3} Quantifier
match 3 of the preceding token
6[-\s]?
[-\s] Character set
character -,   any whitespace character (spaces, tabs, line breaks).
? Quantifier
match between 0 and 1 of the preceding token.
7\d{4}
\d Digit
matches any digit character (0-9)
{4} Quantifier
match 4 of the preceding token

6. Extract Social Security Numbers

Extract Social Security Numbers using Regular Expression.
Extract Social Security Numbers

7. REGEX for Social Security Numbers

The Regular Expression for Social Security Numbers is \b\d{3}-\d{2}-\d{4}\b
Text:
Regular Expression:
123-45-6789\b\d{3}-\d{2}-\d{4}\b
1\b
2123\d{3}
3--
445\d{2}
5--
66789\d{4}
7\b
1\b Word boundary
matches a word boundary.
2\d{3}
\d Digit
matches any digit character (0-9)
{3} Quantifier
match 3 of the preceding token
3- Character
matches a "-" character
4\d{2}
\d Digit
matches any digit character (0-9)
{2} Quantifier
match 2 of the preceding token
5- Character
matches a "-" character
6\d{4}
\d Digit
matches any digit character (0-9)
{4} Quantifier
match 4 of the preceding token
7\b Word boundary
matches a word boundary.

8. Extract Prices

Extract Prices using Regular Expression.
Extract Prices

9. REGEX for Prices

The Regular Expression for Prices is \$\d+(?:,\d{3})*(?:\.\d{2})?
Text:
Regular Expression:
$1,234.56\$\d+(?:,\d{3})*(?:\.\d{2})?
1$\$
21\d+
3,234(?:,\d{3})*
4.56(?:\.\d{2})?
1\$ Character
matches a "$" character
2\d+
\d Digit
matches any digit character (0-9)
+ Quantifier
match 1 or more of the preceding token
3(?:,\d{3})*
(?:
the start of non-capturing group
,
matches a "," character
\d{3}
\d Digit
matches any digit character (0-9)
{3} Quantifier
match 3 of the preceding token
)
the end of non-capturing group
* Quantifier
match 0 or more of the preceding token
4(?:\.\d{2})?
(?:
the start of non-capturing group
.
matches a "." character
\d{2}
\d Digit
matches any digit character (0-9)
{2} Quantifier
match 2 of the preceding token
)
the end of non-capturing group
? Quantifier
match between 0 and 1 of the preceding token.

10. Extract Numbers

Extract Numbers using Regular Expression.
Extract Numbers

11. REGEX for Numbers

The Regular Expression for Numbers is \b\d+\b
Text:
Regular Expression:
10\b\d+\b
1\b
210\d+
3\b
1\b Word boundary
matches a word boundary.
2\d+
\d Digit
matches any digit character (0-9)
+ Quantifier
match 1 or more of the preceding token
3\b Word boundary
matches a word boundary.

12. Extract Dates

Extract Dates using Regular Expression.
Extract Dates

13. REGEX for Dates

The Regular Expression for Dates is \b\d{1,2}/\d{1,2}/\d{2,4}\b
Text:
Regular Expression:
12/25/1985\b\d{1,2}/\d{1,2}/\d{2,4}\b
1\b
212\d{1,2}
3//
425\d{1,2}
5//
61985\d{2,4}
7\b
1\b Word boundary
matches a word boundary.
2\d{1,2}
\d Digit
matches any digit character (0-9)
{1,2} Quantifier
match between 1 and 2 of the preceding token.
3/ Quantifier
matches a "/" character
4\d{1,2}
\d Digit
matches any digit character (0-9)
{1,2} Quantifier
match between 1 and 2 of the preceding token.
5/ Quantifier
matches a "/" character
6\d{2,4}
\d Digit
matches any digit character (0-9)
{2,4} Quantifier
match between 2 and 4 of the preceding token.
7\b Word boundary
matches a word boundary.

14. Extract Times

Extract Times using Regular Expression.
Extract Times

15. REGEX for Times

The Regular Expression for Times is \b(?:[01]?\d|2[0-3]):[0-5]\d(?:\s?[AP]M)?\b
Text:
Regular Expression:
09:00AM\b(?:[01]?\d|2[0-3]):[0-5]\d(?:\s?[AP]M)?\b
1\b
209(?:[01]?\d|2[0-3])
3::
400[0-5]\d
5AM(?:\s?[AP]M)?
6\b
1\b Word boundary
matches a word boundary.
2(?:[01]?\d|2[0-3])
(?:
the start of non-capturing group
[01]?\d Digit
0 to 19
| Alternation
acts like a boolean OR, matches either the expression before or after
2[0-3] Digit
20 to 23
)
the end of non-capturing group
3: Quantifier
matches a ":" character
4[0-5]\d Digit
0 to 59
5(?:\s?[AP]M)?
(?:
the start of non-capturing group
\s?
any whitespace character (spaces, tabs, line breaks),   match between 0 and 1 of the preceding token..
[AP]M Character set
AM or PM
)
the end of non-capturing group
? Quantifier
match between 0 and 1 of the preceding token.
6\b Word boundary
matches a word boundary.

16. 🎉 Finish! 🎉

Author's Note: I hope you can feel the effort I put into these tutorials. I hope to create a series of very easy-to-understand Excel tutorials.

If it is useful, help me share these tutorials, thank you!


Follow me:

Related Tutorials

  • Automatically Format Phone Number Input in Cells
  • How to Enter Multiple Lines of Content in an Excel Cell
  • Use Focus Cell to Highlight Specific Cells
  • Excel Clipboard Tutorial
  • Auto Adjusting Cell Width and Height (AutoFit) in Excel
  • Use Flash Fill to Extract Emails from Text
  • Common Regular Expressions for Excel Users
  • How to Correctly Paste CSV Data into a Worksheet
    • Common Regular Expressions