Complete Guide to Excel REGEX Functions: Clean Data in 10 Minutes with REGEXEXTRACT, REGEXREPLACE, and REGEXTEST

By using Excel REGEX functions together with Copilot “Clean Data,” even beginners can standardize text data in 10 minutes. Copy and paste the samples in this article to reproduce the same results immediately.

Quick Fix: Clean Email Addresses and Phone Numbers in 10 Minutes

1. Run Data → Clean Data to automatically clean up spaces, formatting, and inconsistencies.
2. Extract the domain: =REGEXEXTRACT([@Email],"@(.+)$")
3. Keep only phone number digits: =REGEXREPLACE([@Phone],"[^0-9]","")
4. Format with hyphens: =REGEXREPLACE(REGEXREPLACE([@Phone],"[^0-9]",""),"^(d{3})(d{3,4})(d{4})$","$1-$2-$3")
5. Validate the email address: =REGEXTEST([@Email],"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$")
6. For unsupported environments, use Power Query (Split Column, Replace Values, and Trim).

Why Regular Expressions? Principles and Essential Patterns

• Regular expressions find, extract, and replace text using string patterns.
• Email: ^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$
• Domain only: @(.+)$ / Digits only: [^0-9] / ZIP code (5 digits): ^d{5}$

Practical Examples: Table, Formulas, and Expected Results

Extract the Domain

=REGEXEXTRACT([@Email],"@(.+)$")

Validate an Email Address

=REGEXTEST([@Email],"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$")

Keep Only Phone Number Digits → Format with Hyphens

=REGEXREPLACE([@Phone],"[^0-9]","")

=LET(
 n, REGEXREPLACE([@Phone],"[^0-9]",""),
 IF(REGEXTEST(n,"^d{11}$"), REGEXREPLACE(n,"^(d{3})(d{4})(d{4})$","$1-$2-$3"),
 IF(REGEXTEST(n,"^d{10}$"), REGEXREPLACE(n,"^(d{3})(d{3})(d{4})$","$1-$2-$3"),
 n)))

Identify ZIP Codes

=REGEXTEST([@Address],"bd{5}b")

LAMBDA Automation

=LAMBDA(text,
 LET(n,REGEXREPLACE(text,"[^0-9]",""),
  IF(REGEXTEST(n,"^d{11}$"),REGEXREPLACE(n,"^(d{3})(d{4})(d{4})$","$1-$2-$3"),
  IF(REGEXTEST(n,"^d{10}$"),REGEXREPLACE(n,"^(d{3})(d{3})(d{4})$","$1-$2-$3"),n))))

Alternatives, Notes, and Checklist

Copilot Clean Data

Use Clean Data for preprocessing first, then finish formatting with REGEX.

Power Query / Text Functions

• You can use Split Column, Replace Values, and Trim as alternatives to REGEX.
• Combine TEXTSPLIT, LEFT/MID/RIGHT, SUBSTITUTE, and SEARCH/FIND.

Troubleshooting

Conclusion

Clean up data in 10 minutes with the combination of Excel REGEX functions and Copilot Clean Data! The next article covers parsing automation with TEXTSPLIT, WRAPROWS, and VSTACK.