How to Remove Spaces in Excel

Mar 17, 2020 • edited Mar 18, 2020

Sometimes, when you copy data from other locations and pasted them at Excel worksheet, there may be some extra spaces leaving at front or the end of strings in cells. It is time-consuming to delete the spaces one by one for making data looks tidy. Here this tutorial introduces the smart and easy ways help you to quickly remove leading and trailing spaces.

The TRIM function in Excel removes leading spaces, extra spaces and trailing spaces. Use the SUBSTITUTE function to remove all spaces or non-breaking spaces.

  1. The TRIM function below removes 2 leading spaces, 3 extra spaces and 2 trailing spaces.

Remove Spaces in Excel

Note: the TRIM function does not remove single spaces between words.

  1. To get the length of a string, use the LEN function.

Len Function

Explanation: the LEN function counts 2 spaces, 2 characters, 4 spaces, 3 characters and 2 spaces.

  1. You can use the SUBSTITUTE function to remove all spaces.

Remove All Spaces

Note: the SUBSTITUTE function substitutes spaces (second argument) with empty strings (third argument).

Text imported from other applications may contain ASCII characters. The CLEAN function removes the first 32 non-printable ASCII characters (codes 0 through 31).

  1. The CLEAN function below removes a non-printable character, CHAR(7).

Clean Function

  1. Simply combine the CLEAN and the TRIM function to remove non-printable characters and spaces.

Clean and Trim Function

  1. The CLEAN function removes line breaks. To enter a line break, press ALT + ENTER.

Remove Line Breaks

  1. You can also use the SUBSTITUTE function to remove a line break, CHAR(10), and replace it with something else. For example, a comma and a space.

Substitute Line Breaks

The CLEAN function doesn't remove all ASCII characters. Simply use the SUBSTITUTE function to remove other ASCII characters.

  1. First, find out the code number of the character.

Code Number

  1. Next, use the SUBSTITUTE and the CHAR function to return the text string without this character.

Substitute Function

  1. If your text contains non-breaking spaces, CHAR(160), the TRIM function doesn't work.

Non-breaking Spaces

  1. You can use the SUBSTITUTE function to substitute non-breaking spaces, CHAR(160) with normal spaces, CHAR(32). Don't forget to add a TRIM function to remove the extra spaces.

Remove Non-breaking Spaces

#Tutorial#How To#Functions

How to Use Two-column Lookup in Excel

How To Use Calculate Age TODAY function In Excel