Remove the dashes with SUBSTITUTE
Step 1 of 3
SUBSTITUTE swaps one text for another wherever it appears: =SUBSTITUTE(text, old_text, new_text). It works on every match, not just the first.
Click Continue when you're ready.
Already know it? Type the formula straight into B2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Phone as typed | Phone | ||||
| 2 | 020-7946-0018 | |||||
| 3 | ||||||
| 4 | ||||||
| 5 | ||||||
| 6 | ||||||
| 7 | ||||||
| 8 |
Need help?
Why this works
SUBSTITUTE(A2,"-","") looks for every "-" in A2 and puts "" in its place, which removes it. The result is text, so the leading 0 stays. Give a fourth argument, like 1, to replace only the first dash.
Common mistake: Writing " " (a space) when you mean "" (nothing). The dashes then turn into spaces.
The answer
- B2
=SUBSTITUTE(1A2,2"-",3"")
In plain words: In 1the phone number in A2, replace 2every dash with 3nothing.
=SUBSTITUTE(A2,"-","") removes every dash from A2. Replace the "-" with whatever character you want gone.
+10 points