Cellwise Try it free

Excel guide · 2 min read

How to remove spaces in Excel

Quick answerUse =TRIM(A2) to remove leading and trailing spaces and shrink double spaces between words to one. To remove every space, use =SUBSTITUTE(A2," ","") or press Ctrl+H, type a space in Find what, leave Replace with empty, and click Replace All.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1CustomerPhoneSKU
2 Olivia Brown 555 012 3344AB - 1001
3Noah Wilson 555 987 6610AB - 1002
4 Emma Davis555 443 2018CD - 2001
5Liam Moore555 220 9071CD - 2002

Remove extra spaces with TRIM

TRIM is the right tool for names, addresses and anything with words. It strips spaces from both ends and turns runs of spaces between words into a single space. It never removes the one space you need between words.

Put the formula in a helper column, fill it down, then paste the results as values over the original if you want to replace it.

=TRIM(A2)

Result: Olivia Brown

Turns " Olivia Brown " into "Olivia Brown".

Prove the spaces were there with LEN

Extra spaces are invisible, which is why lookups and duplicate checks fail for no obvious reason. Compare the length before and after TRIM to see how many characters were junk.

=LEN(A2)-LEN(TRIM(A2))

Result: 5

Returns 5, the number of extra spaces in A2.

Remove all spaces with SUBSTITUTE

For codes, phone numbers and IDs you usually want no spaces at all. SUBSTITUTE replaces every space with nothing.

The result is text. If you need a real number, wrap it: =VALUE(SUBSTITUTE(B2," ","")). Long IDs and numbers with leading zeros are safer left as text.

=SUBSTITUTE(B2," ","")

Result: 5550123344

Returns "5550123344".

Remove spaces with Find & Replace (no formula)

Find & Replace changes the cells in place, so there is no helper column to clean up. It removes every space, so do not use it on names.

  1. Select the cells to clean, for example C2:C5.
  2. Press Ctrl+H, or go to Home > Find & Select > Replace.
  3. In Find what, type a single space.
  4. Leave Replace with empty.
  5. Click Replace All. "AB - 1001" becomes "AB-1001".

When TRIM does not work: non-breaking spaces

Data copied from websites often contains non-breaking spaces (character 160). TRIM only removes the normal space (character 32), so these survive and look identical.

Swap them for normal spaces first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). With Find & Replace, click in Find what and hold Alt while typing 0160 on the number pad, then replace with nothing or a normal space.

Common mistakes

Using SUBSTITUTE on names

It removes the space between words too, giving "OliviaBrown". Use TRIM for text with words.

TRIM seems to do nothing

The space is probably a non-breaking space from the web. Use TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Deleting the original column breaks the formulas

Copy the TRIM column and use Paste Values before you delete or overwrite the source.

Phone numbers lose their leading zero

Do not convert cleaned IDs to numbers. Keep the SUBSTITUTE result as text.

FAQ

How do I remove trailing spaces in Excel?

Use =TRIM(A2). It removes trailing and leading spaces in one go, and also reduces double spaces between words to one.

How do I remove spaces between numbers?

Use =SUBSTITUTE(A2," ","") or Find & Replace with a space in Find what and nothing in Replace with. Both remove every space.

Does TRIM remove line breaks?

No. Line breaks are character 10. Use =SUBSTITUTE(A2,CHAR(10)," ") to swap them for spaces, or CLEAN to strip non-printing characters.

Why does my VLOOKUP fail when the values look the same?

One side usually has a hidden trailing space. Check with LEN, then clean the column with TRIM.

Functions used

Related guides

Updated