📎 This article includes 1 downloadable practice file ↓
In this article
REGEXREPLACE replaces text matching a pattern — strip every non-digit, collapse repeated spaces, mask numbers.
Syntax
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])| Argument | What it means |
|---|---|
replacement |
Can use $1, $2 for captured groups. |
Examples
Example 1
=REGEXREPLACE(A2, "[^0-9]", "")Keep digits only.
Example 2
=REGEXREPLACE(A2, "\d(?=\d{4})", "X")Mask all but the last four digits.
REGEXREPLACE in practice
Where you will use it
- Mask numbers
- Remove extra spaces
- Standardise phone formats
Worked example
| Text | Formula | Result |
|---|---|---|
| Card 4111 1111 1111 1234 | =REGEXREPLACE(A2,"\d{4} ","XXXX ") | Card XXXX XXXX XXXX 1234 |
| too many spaces | =REGEXREPLACE(A3," +"," ") | too many spaces |
| +91-98765-43210 | =REGEXREPLACE(A4,"[^\d]","") | 919876543210 |
Worked out step by step, not calculated here: REGEXREPLACE needs the latest Microsoft 365.
Mistakes people make
| Mistake | What to do instead |
|---|---|
| Greedy patterns | .* matches as much as possible |
| Case | Use the case_sensitivity argument |
Related functions
📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.
⚡ Interactive Practice Lab & Formula Tester:
Test formulas dynamically in our web formula laboratory or download the companion Excel workbook.
📎 Practice files for this article
- 📗Practice workbook: Text & cleaning24 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.⬇ XLSX · 35 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong