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.
TRIM function
The TRIM function will remove both leading and trailing spaces. It will keep only one space between words. You can see an example of that in cell C7.
CLEAN Function
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.
1 |
=TRIM(CLEAN(B2)) |
SUBSTITUTE function
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 SUBSTITUTE function.
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.
1 |
=TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160)," "))) |
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.
Using a free Excel plugin
The last method you can use to get rid of leading and trailing spaces is to use an SEO Excel plugin. You can find it on the main page.
After you install the plugin, you can navigate to SEO >> Text >> Trim and Combine >> Trim Whitespaces.
Notice that this button only removes leading and trailing spaces but doesn’t combine them. If you want to convert multiple adjacent spaces, use the Combine Spaces button.
This feature will take multiple spaces and convert them into one.