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:

DOWNLOAD EXCEL WORKBOOK

 

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!

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

leading spaces

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

leading spaces

 

How to Clean Leading and Trailing Spaces

 leading spaces

HELPFUL RESOURCE:

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+