SUBSTITUTE vs REGEXREPLACE
SUBSTITUTE swaps an exact text for another; REGEXREPLACE swaps anything that fits a pattern.
| SUBSTITUTE | REGEXREPLACE | |
|---|---|---|
| Available in | Every version | Excel 365 |
| What it finds | An exact text | A pattern: digits, letters, spaces… |
| Remove everything but digits | Several nested SUBSTITUTEs | One formula: "\D" with "" |
| Case | Case-sensitive | Case-sensitive unless you pass 1 |
Syntax and example
SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text, [instance_num])
=SUBSTITUTE(A2,"-","")
Replaces text with other text inside a cell, every time it appears.
REGEXREPLACE
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
=REGEXREPLACE(E2,"\D","")
Replaces whatever matches a pattern: remove everything that is not a digit, change formats, clean codes.
Which one to use?
To change one specific word or symbol, SUBSTITUTE. To clean varied formats (phones, codes), REGEXREPLACE.
About Ads
This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.
Ads help us keep WebHerramientas free for everyone
Practice it
Solve the exercise on the sheet; it is checked instantly. See all lessons · Formula translator
Regular expressions: REGEXTEST, REGEXEXTRACT and REGEXREPLACE
A regular expression describes a text pattern: \d is a digit, \D anything that is not, [A-Z] a capital letter, {4} repeats 4 times, + one or more, ^ and $ mark start and end. REGEXTEST says whether the text fits the pattern, REGEXEXTRACT pulls out the matching part and REGEXREPLACE replaces it.
=REGEXTEST(A2,"^[A-Z]{3}-\d{4}$")REGEXPRUEBA = REGEXTESTREGEXEXTRACCION = REGEXEXTRACTREGEXREEMPLAZAR = REGEXREPLACEIn B2, check whether each code has exactly 3 capital letters, a hyphen and 4 digits.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Code | Valid? | Message | Number | Phone | Digits only | |
| 2 | MEX-2041 | Order 4521 shipped | 55-1234-5678 | ||||
| 3 | mx-2018 | Invoice 778 pending | (33) 9876 5432 | ||||
| 4 | GDL-9932 | Ticket 30215 open | 81 2233 4455 | ||||
| 5 | QRO-12345 | Customer 66 called | +52 222 111 0099 | ||||
| 6 | MTY-7781 | Shipment 1290 delayed | 442.555.7788 | ||||
| 7 |
Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.
Frequently asked questions
- What is the difference between SUBSTITUTE and REGEXREPLACE?
- SUBSTITUTE swaps an exact text for another; REGEXREPLACE swaps anything that fits a pattern.
- When should I use SUBSTITUTE or REGEXREPLACE?
- To change one specific word or symbol, SUBSTITUTE. To clean varied formats (phones, codes), REGEXREPLACE.