Find and replace with SUBSTITUTE

1 / 5

These phone numbers were typed with dashes. Swap every dash for a space in column B.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABC
1PhoneWith spaces
207700-900393
307700-900-168
407700-900683
507700-900351
607700-900-884
707700-900-980
807700-900413
9
10
11
12
phone numbers
with spaces
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How SUBSTITUTE works

  • What does SUBSTITUTE do?

    SUBSTITUTE is find and replace inside a formula. It swaps every copy of one piece of text for another:

    =SUBSTITUTE(A2,"-"," ")

  • What are its parts?

    • The text to change: A2.
    • What to find, in double quotes: "-".
    • What to put instead: " ". Use "" (two quotes, nothing between) to delete it.
    • Optional: which copy to replace. =SUBSTITUTE(A2,"-"," ",2) only changes the second dash.

  • Why not TRIM?

    TRIM only removes spaces at the start and end, and turns double spaces into single ones. The spaces in the middle of 07700 900 123 stay. SUBSTITUTE removes every one.

  • Is it case-sensitive?

    Yes. SUBSTITUTE(A2,"ltd","Ltd") won't touch "LTD". Use it on UPPER or LOWER of the text if the capitals vary.

  • Why not Find and Replace (Ctrl+H)?

    Ctrl+H changes the original data once. SUBSTITUTE leaves the original alone and updates by itself when the data changes. It also works inside bigger formulas, for example to clean a value before a lookup.

Know someone fighting with a spreadsheet? Send them this page.