Unwanted spaces can break formulas, formatting, and be the source of hard to track down errors. Here are a couple ways to remove them.
Contents:
How to Remove Spaces in Excel
How to Remove Leading or Trailing Spaces Text
Leading or trailing spaces that come before or after a word like: " text ", can be almost impossible to see and can cause all sorts of errors if you're referencing those cells in other formulas.
Luckily, there is a simple formula we can use to remove leading and trailing spaces from any text in any cell.
The TRIM formula exists for just this purpose. The formula is as simple as:
=TRIM(A1)Where A1 is the cell that you wish to remove the spaces from.
For example, let's take this list of last names, which have spaces in front, and behind each name. There are some names with just one space, and others with multiple.
To remove the spaces from the beginning and end, we can insert the TRIM formula to the right.
And drag down to trim any leading or trailing spaces off.
How to Remove All Spaces from a Text in a Cell
If you want to remove every space, not just leading or trailing, from a cell, there is a different formula we can use.
The SUBSTITUTE function can be used to delete all of the spaces found in a string of text.
Simply replace "A2" with the cell containing spaces that you want to remove. The first " " is the old text that we wish to remove, and the "" is the text we wish to replace it with (nothing in this case)
=SUBSTITUTE(A2," ","")
So, if we have these cells with text in them, in C13, and we want to remove the spaces between the words, we can just drop this formula next to them on the right.
=SUBSTITUTE(C13," ","")