1. Common Regular Expressions
Here is a series of commonly used regular expressions:
Theses regular expressions are used in
EmailsPhone NumbersSocial Security NumbersPricesNumbersDatesTimes Theses regular expressions are used in
REGEXEXTRACT function.
2. Extract Emails
Extract Emails using Regular Expression.
3. REGEX for Emails
The Regular Expression for Emails is
[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}Text:
Regular Expression:
john.doe@company.com[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}1
john.doe[a-zA-Z0-9._%+-]+2
@@3
company[a-zA-Z0-9.-]+4
.\.5
com[a-zA-Z]{2,}1
[a-zA-Z0-9._%+-]+ [a-zA-Z0-9._%+-] Character set., character _, character %, character +, character -.+ Quantifier2
matches a "@" character@ Character3
[a-zA-Z0-9.-]+ [a-zA-Z0-9.-] Character set., character -.+ Quantifier4
matches a "." character\. Character5
[a-zA-Z]{2,} [a-zA-Z] Character set{2,} Quantifier4. Extract Phone Numbers
Extract Phone Numbers using Regular Expression.
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
(\(2
123\d{3}3
)\)4
\s?5
456\d{3}6
-[-\s]?7
7890\d{4}1
matches a "(" character\( Character2
\d{3} \d Digit{3} Quantifier3
matches a ")" character\) Character4
\s? \s Character set? Quantifier5
\d{3} \d Digit{3} Quantifier6
[-\s]? [-\s] Character set-, any whitespace character (spaces, tabs, line breaks).? Quantifier7
\d{4} \d Digit{4} Quantifier6. Extract Social Security Numbers
Extract Social Security Numbers using Regular Expression.
7. REGEX for Social Security Numbers
The Regular Expression for Social Security Numbers is
\b\d{3}-\d{2}-\d{4}\bText:
Regular Expression:
123-45-6789\b\d{3}-\d{2}-\d{4}\b1
\b2
123\d{3}3
--4
45\d{2}5
--6
6789\d{4}7
\b1
matches a word boundary.\b Word boundary2
\d{3} \d Digit{3} Quantifier3
matches a "-" character- Character4
\d{2} \d Digit{2} Quantifier5
matches a "-" character- Character6
\d{4} \d Digit{4} Quantifier7
matches a word boundary.\b Word boundary8. Extract Prices
Extract Prices using Regular Expression.
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
$\$2
1\d+3
,234(?:,\d{3})*4
.56(?:\.\d{2})?1
matches a "$" character\$ Character2
\d+ \d Digit+ Quantifier3
(?:,\d{3})* (?: , \d{3} \d Digit{3} Quantifier) * Quantifier4
(?:\.\d{2})? (?: . \d{2} \d Digit{2} Quantifier) ? Quantifier10. Extract Numbers
Extract Numbers using Regular Expression.
11. REGEX for Numbers
The Regular Expression for Numbers is
\b\d+\bText:
Regular Expression:
10\b\d+\b1
\b2
10\d+3
\b1
matches a word boundary.\b Word boundary2
\d+ \d Digit+ Quantifier3
matches a word boundary.\b Word boundary12. Extract Dates
Extract Dates using Regular Expression.
13. REGEX for Dates
The Regular Expression for Dates is
\b\d{1,2}/\d{1,2}/\d{2,4}\bText:
Regular Expression:
12/25/1985\b\d{1,2}/\d{1,2}/\d{2,4}\b1
\b2
12\d{1,2}3
//4
25\d{1,2}5
//6
1985\d{2,4}7
\b1
matches a word boundary.\b Word boundary2
\d{1,2} \d Digit{1,2} Quantifier3
matches a "/" character/ Quantifier4
\d{1,2} \d Digit{1,2} Quantifier5
matches a "/" character/ Quantifier6
\d{2,4} \d Digit{2,4} Quantifier7
matches a word boundary.\b Word boundary14. Extract Times
Extract Times using Regular Expression.
15. REGEX for Times
The Regular Expression for Times is
\b(?:[01]?\d|2[0-3]):[0-5]\d(?:\s?[AP]M)?\bText:
Regular Expression:
09:00AM\b(?:[01]?\d|2[0-3]):[0-5]\d(?:\s?[AP]M)?\b1
\b2
09(?:[01]?\d|2[0-3])3
::4
00[0-5]\d5
AM(?:\s?[AP]M)?6
\b1
matches a word boundary.\b Word boundary2
(?:[01]?\d|2[0-3]) (?: [01]?\d Digit| Alternation2[0-3] Digit) 3
matches a ":" character: Quantifier4
0 to 59[0-5]\d Digit5
(?:\s?[AP]M)? (?: \s? [AP]M Character set) ? Quantifier6
matches a word boundary.\b Word boundary16. 🎉 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