Nearly every computer user in this modern age has spent his or her fair share of time tinkering around with Excel, trying to coax those silly little cells into behaving just as we need them to, but often they never turn out quite as we expect.
Today we’ll examine a few simple techniques to ensure that all your text data in Excel (or Google Docs) is clean and tidy by eliminating any and all leading or trailing spaces.
- 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.
- If your data set contains superfluous spaces, the Excel TRIM function can help you delete them all in one go - leading, trailing and multiple in-between spaces, except for a single space character between words. A regular TRIM formula is as simple as this: =TRIM (A2) Where A2 is the cell you want to delete spaces from.
- For example, if you want to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column's formulas to values, and then removing the original column. The basic steps for cleaning data are as follows: Import the data from an external data source.
In order to remove leading spaces in Excel, we can use Excel TRIM function. However, TRIM in Excel does not remove trailing spaces in Excel. The formula is a combination of TRIM, CLEAN, SUBSTITUTE & CHAR functions. In cell B2, the formula is =TRIM (CLEAN (SUBSTITUTE (A2,CHAR (160),' '))) In this way, Excel remove spaces which are not required.
The Trim function
The most obvious (and generally efficient) method for removing both leading and trailing space is to use the TRIM() function. As stated in the official documentation, TRIM() “removes all spaces from text except between words.” Unfortunately, in the case of both Excel and Google Docs, that simply isn’t always true, as we can easily illustrate with a bit of code.
In a new sheet, begin by inserting a single word into the first cell of column A, or cell A1, paying special attention to include a few spaces before and after the word. My example of A1 contains:
Column B, will be our first test column, and the simplest test for our purposes is to check the length of the text in our cells in column A, using the lovely LEN() function.
To display the length of cell A1 (which in my example text is 18 characters long), the contents of cell B1 should look like this:
Nothing fancy, but this will display the total length of our cell from column A.
Now we’ll try out TRIM(), so modify cell C1 like so:
The text will be obviously trimmed to the naked eye, but just to verify, modify cell D1 this time to check the length of our TRIMMED value.
This will be noticeably shorter as expected (10 characters in this example).
Non-Breaking Spaces
As it happens, often when importing text into Excel or using copy/paste, we’re inadvertently adding non-breaking space characters to the text, which are not properly handled as expected by the TRIM() function.
To illustrate, add a new row to our test sheet by modifying cell A2:
In Excel, the CHAR() function allows us to output characters based on the numeric identifier of that character. In this case for testing, CHAR(160) is a non-breaking space, so the contents of A2appears to match that of A1, but in fact the spaces on either side for A2 are non-breaking.
Now copy/paste the three test cells B1:D1 and paste them into row 2 starting at B2. We’ll now clearly see the problem with TRIM() – while it worked in row 1 and removed all the normal spaces, it doesn’t do anything in row 2 and thus our ‘TRIMED’ version is still the full 18-length string.
Absolutely Removing the Excess
To fully resolve our issue and ensure we always remove leading and trailing spaces of all kinds, copy all four cells in row 2 down into row 3, so rows 2 and 3 are presently identical.
The trick is to combine TRIM() with a few more functions.
We start by using SUBSTITUTE() to replace all instances of CHAR(160) (non-breaking space) with normal space characters.
We also use the CLEAN() function which attempts to remove characters that simply wouldn’t display on your machine in the first place.
The end result is the following code, which should replace what you have in cell C3:
Voila! We’re now able to remove all leading and trailing spaces in Excel (and Google Docs) no matter what type of space it is. To apply this to other cells in the column, just copy and paste it, changing the cell specification above from A3 to whatever is necessary to get started.
You have two Excel lists, and you’re trying to find the items that are in both lists. You know there are matching items, but if your VLOOKUP formulas can’t find any matches, you might need to clean Excel data with TRIM and SUBSTITUTE.
VLOOKUP Cannot Find Matches
Here’s an example of this problem. In this screenshot, the tiptech.html page is in both lists, but the VLOOKUP formula in cell C2 can’t find it. It returns and #N/A error.
Spot the Differences
Working with Excel data can be like one of those “Spot the Difference” puzzles. What’s different between list A and list B?
If you’re lucky, the differences are obvious, like the forward slash in column B, and no leading slash in column E. Other times, it’s tougher to find the differences.
How To Delete Trailing Spaces In Excel Cell
A common problem, when trying to match data, is items with leading or trailing spaces. You can’t see them on the screen, but after you’ve encountered them a few times, you learn to check for them.
Check the Length
The LEN function is a great help if you suspect there are hidden space characters in a cell.
If you use the LEN function to compare the length of the text in cell B2 and E4, you’d see that there are 2 additional characters in cell B2. One character is the forward slash, and the other character is a trailing space.
Remove Leading and Trailing Spaces
If you want to use a VLOOKUP or MATCH to find column B items, in column E, you’ll have to get rid of any extra characters.
First, you can deal with the spaces, by using the TRIM function.
To return the text from cell B2, without any leading or trailing characters, you’d use this formula:
=TRIM(B2)
If you use a formula in cell B12 to check the length of the trimmed text, it’s now 13 characters, instead of 14. The trailing space has been removed.
Remove a Specific Character
Next, you can use the SUBSTITUTE function to remove the forward slash from the text in cell B2.
=SUBSTITUTE(B2,”/”,””)
- The first argument, B2, is the cell that contains the text value.
- The second argument, “/”, is the old text, that you want to replace.
- The third argument, “”, is the new text, that replaces the old text. If you want to remove the old text, without inserting new text, use “” as an empty string, as we did here.
Combine the Functions
The TRIM and SUBSTITUTE functions work well separately, and you can combine them, to remove the extra spaces and the forward slash. The order doesn’t matter, so you can use either:
=TRIM(SUBSTITUTE(B2,”/”,””))
or:
=SUBSTITUTE(TRIM(B2),”/”,””)
Add to the VLOOKUP
Now that you know the TRIM and SUBSTITUTE functions will clean up the text in column B, you can add those functions to the VLOOKUP formula.
Instead of using B2 in the VLOOKUP:
=VLOOKUP(B2,$E$2:$F$8,2,FALSE)
use the TRIM and SUBSTITUTE functions:
=VLOOKUP(TRIM(SUBSTITUTE(B2,”/”,””)),$E$2:$F$8,2,FALSE)
A Match is Found
After you change the VLOOKUP formula, to include TRIM and SUBSTITUTE, it works correctly.
A match for the cleaned up text is found in column E, and in the Update column, cell C2 is filled in with the correct date.
Troubleshoot a VLOOKUP Formula
Remove Trailing Spaces In Excel Numbers
If TRIM and SUBSTITUTE don’t solve your VLOOKUP problems, there are a few more suggestions on the Contextures website: Troubleshoot the VLOOKUP formula.
Delete Trailing Spaces In Excel Cell
On that page, you’ll also find examples of using the IF function or IFERROR function to deal with VLOOKUP function errors.
Delete Trailing Spaces In Excel
_____________