At first instance, you may wonder why we would need to remove spaces in Excel. If you have unnecessary spaces it may prevent working of some functions, filtering etc. You may notice leading and double spaces, but it is very difficult to identify spaces as you cannot see it. You can only see it if you highlight the cell content when editing. It will be an extremely difficult task to remove the spaces manually when you have a large data set. So, we are here explaining Excel functions to easily remove spaces.

• TRIM() function

Trim function is used to remove leading, trailing and double spaces between text strings.

Drag down the (D1) cell to copy the formula to all cells. It will remove all the unnecessary spaces within text strings. It has also removed the extra spaces between words.

• Find and Replace

This method can be used to remove double spaces and all spaces.

Selects the cells > go to Home > Find and Replace (or Ctrl + H)

Type double spaces in Find what and single space in Replace with > Replace All

This will then replace all double spaces with single space. This would not remove leading and trailing spaces. Also if there any triple spaces, then it would become double space because only one space would be removed. Then you will have to use function again to find any remaining double spaces.

All spaces can be removed using this method by typing single space in Find what and leave blank for Replace with.