There are a few different ways you can use to clean imported data from unwanted leading or trailing spaces. In this lesson, I will show you how you can achieve this using the following Excel functions: TRIM, CLEAN, SUBSTITUTE, and FIND & REPLACE.
The TRIM function will remove both leading and trailing spaces. It will keep only one space between words. You can see the example of that in cell C7.
Sometimes when you import data you can find not only spaces but other, non-printable characters, such as line breaks, etc. The CLEAN function will remove them. When you combine this function with the TRIM function you will get a formula that will remove those unwanted characters and spaces.
The CLEAN function removes all unwanted non-printable characters, but it’s not able to remove a non-breaking space, which is represented in Excel as CHAR(160). For that reason, we will use the SUBSTITUTEfunction.
This function changes one character into another. The SUBSTITUTE function is executed inside the TRIM function, so CHAR(160) will first be replaced with space, then spaces will be removed by the TRIM function.
Find & Replace
Another way to remove spaces is by using the Find and Replace feature. Just select cells with spaces, then go to HOME >> Editing >> Find & Select >> Replace. Alternatively, you can use the Ctrl + F keyboard shortcut and select the Replace tab. In Find what: enter a space and hit Replace All.
It will replace all leading and trailing spaces, but also those between words.
This is the result.