How to / Text

How to remove extra spaces in Excel

Use =TRIM(A2). It removes the spaces at the start and the end, and leaves one space between words.

A name with spaces in the wrong places

ABC
1Name
2 Ana Silva Ana Silva
3ben LEE
4CHLOE o'neil

In C2: =TRIM(A2) → Ana Silva

A2 holds two spaces in front, three in the middle and one at the end.

How it works

  1. TRIM deletes every space before the first character and after the last.
  2. Between words, it reduces any run of spaces to one.
  3. The result is a new value; the original cell is unchanged.

Variations

  1. How many characters were removed

    ABC
    1Name
    2 Ana Silva 5
    3ben LEE
    4CHLOE o'neil

    In C2: =LEN(A2)-LEN(TRIM(A2)) → 5

    A quick test for hidden spaces: anything above 0 means the cell had some.

  2. Clean the spaces and the capitals together

    ABC
    1Name
    2 Ana Silva Ana Silva
    3ben LEE
    4CHLOE o'neil

    In C2: =PROPER(TRIM(A2)) → Ana Silva

  3. Remove every space, including those between words

    ABC
    1Name
    2 Ana Silva AnaSilva
    3ben LEE
    4CHLOE o'neil

    In C2: =SUBSTITUTE(A2, " ", "") → AnaSilva

    Useful for phone numbers and codes.

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related