Keep only digits with REGEXREPLACE
PremiumIn B2, use REGEXREPLACE to remove everything from A2 that is not a digit. \D (capital D) means any character that is not a digit.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Phone as typed | Digits only | ||||
| 2 | Tel: 020 7946 0018 | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Premium lesson
Unlock REGEX functions and every other lesson
This is a premium lesson. Premium lets you solve it and every other lesson, take the timed mock tests, and earn certificates you can share on LinkedIn.
Next with premium:IFCOUNTIF
Need help?
Why this works
REGEXREPLACE replaces every match of the pattern. \D matches the letters, the colon and the spaces, and "" puts nothing in their place, so only the digits are left. The result is text, which keeps the leading 0.
Common mistake: Using \d instead of \D. The small d means digits, so the digits disappear instead.
The answer
- B2
=REGEXREPLACE(1A2,2"\D",3"")
In plain words: In 1the phone number in A2, replace 2everything that is not a digit with 3nothing.