Lesson 148: How to remove spaces in Excel

TRIM function in Excel Remove leading, trailing, and trailing spaces. Use the SUBSTITUTE function to remove all spaces or spaces.

How to remove spaces in Excel

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

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

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

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

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

5. Simply combine CLEAN and TRIM to remove non-printable characters and spaces.

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

7. You can also use the SUBSTITUTE function to remove the line break, CHAR(10) and replace it with something else. For example, commas and spaces.

The CLEAN function does not remove all ASCII characters. Just use the SUBSTITUTE function to remove other ASCII characters.

8. First, figure out the character’s code.

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

10. If your text contains non-breaking spaces, CHAR(160), the TRIM function does not work.

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