Leading spaces (at the start of the text) and Trailing spaces (at the end of the text) usually get in the way when we want to perform operations in Excel.

Whether it leading or trailing spaces, we already have a couple ways how to clean them in Excel.

For example using the TRIM formula:

The spaces get removed with no issues:


However if we have non-breaking spaces, the TRIM formula will not remove these spaces:

The leading space is still there!

What are non-breaking spaces you ask?

Sometimes data downloaded as text uses non-breaking spaces for formatting purposes i.e. it prevents an automatic line break in between these spaces.

This is represented by CHAR(160):

This looks like a pain! How do we clean these? I explain how you can do this below:



STEP 1: We will use the SUBSTITUTE Formula to remove the non-breaking spaces depicted by CHAR(160)

The goal is to replace the non-breaking spaces with normal spaces.

=SUBSTITUTE(C9, CHAR(160), ” “)

leading spaces


STEP 2: Now that we do not have the non-breaking spaces anymore, let us use the TRIM Formula!


leading spaces

Now all of your leading and trailing spaces are cleaned up!

leading spaces


How to Clean Leading and Trailing Spaces


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn