For some reason, your VLOOKUP‘s are returning an #N/A error and you have no idea why. Everything looks good and the formula should be working. But for some unknown reason, a handful of results are errors. The first time this happened to me, I spent ages trying to find the problem only to discover some of the lookup data had extra space characters attached at the end! This happens a lot. The data may have space characters at the start or at the end. It may even have multiple spaces between words that shouldn’t be there. Now that we know the problem, how can we fix it? In this post, we’ll look at a variety of methods to remove these unwanted extra space characters from our data.
Our Example Data for the Post
For this post, we’ll look at a simple set of data. It’s just “Hello World!” over and over again but with various extra spaces thrown in. Let’s look at how we can fix all these up!
Removing Extra Spaces with the TRIM Function
The first method we’ll look at is the simplest.
Extra spaces is such a common problem, that there’s a function to remove (or trim) them. It’s the TRIM function.
It does exactly what it sounds like it might do. It trims off any excessive spaces from the data. It removes all spaces from a text string except for single spaces between words. The TRIM function has one required input, and this is the text from which we want to trim off excess spaces. The input can either be a cell reference like B2 or a hardcoded string like ” Hello World! “. TRIM(B2) would return the contents of cell B2 with extra spaces removed. TRIM(" Hello World! “) would return “Hello World!“.
Removing Extra Spaces Between Words with Find and Replace
The next method will use Excel’s find and replace search feature. This will only work for removing extra spaces between words.
First, we need to select the rage of cells to which we want to remove extra space. Then go to the Home tab ➜ Editing section ➜ press the Find & Select button ➜ choose the Replace option from the menu. You can also use the Ctrl + H keyboard shortcut.
Both will open the Find & Replace menu and we can press the Options button for more advance find and replace options.
In the Find what section, we need to add two space characters. We can press the Space bar twice on the keyboard. In the Replace with section, we need to add a single space character. We can press the Space bar once on the keyboard. We can tell the space characters are in the input areas because the cursor will move to the right each time we press the space bar. Make sure the other advanced options selected are Within Sheet and Look in Formulas. Press the Replace All button. Since there are strings of 5 spaces in our data we will need to press the Replace All button a few times until it can no longer find any double space characters in the data. This will leave a single space at the start or end of the data if there were any spaces there.
Removing Extra Spaces with Power Query
Power Query is all about data transformation, so it should be able to handle this task.
Select the data and go to the Data tab ➜ press the From Table/Range command.
This will turn our data into an Excel Table, if it’s not already formatted as one. We can make sure the data range selected is correct and adjust it if needed. Our data didn’t have any column headings, so we’ll leave the My table has headers box unchecked. Press the OK button to create the table and the power query editor will open up.
Right click on the column heading ➜ choose Transform ➜ select Trim.
Unfortunately, this doesn’t remove any extra spaces between the words and we need to implement a couple Replace Values transformation steps to accomplish this. Right click on the column and select Replace Values from the menu.
Like the find and replace method, we will need to add two spaces as the Value To Find and a single space as the Replace With value. We can then press the OK button to add this transformation. We need to repeat this step until all the extra spaces have been removed from our data. To get the cleaned data back into Excel, we need to close and load the data. We can go to the Home tab in the power query editor ➜ press Close & Load ➜ choose Close & Load To ➜ choose a Table and select the location to load the table.
Removing Extra Spaces Between Words With VBA
VBA also has a trim function built in. The code above will loop through each cell in a selected range and apply the VBA Trim function to the value inside the cell. Similar to the power query Trim transformation, it will only remove the spaces at the start and end of any text.
Conclusions
There are lots of methods available for removing any extra spaces found in our data. We took a look at formulas, find and replace, power query and VBA. In my opinion, the simple TRIM worksheet function is the easiest and most effective way.
