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), ” “)
STEP 2: Now that we do not have the non-breaking spaces anymore, let us use the TRIM Formula!
=TRIM(SUBSTITUTE(C9, CHAR(160), ” “))
Now all of your leading and trailing spaces are cleaned up!
How to Clean Leading and Trailing Spaces