Extra spaces can make it difficult to search through your data and might even lead to mistakes in your formulas. Learning how to remove spaces in Excel can help you avoid these issues and keep your data clean and accurate.

Why is my Excel VLOOKUP Not Working?

Imagine you’re trying to use a function in Excel to find a part number in a list and get its description. Even though the number you’re looking for seems to be in the list, the VLOOKUP function can’t find it and shows an error.

Excel VLOOKUP not working

This error can happen for many reasons, but it’s often because of extra spaces before or after the text you’re searching for or in the list. Excel can spot these extra spaces at the beginning or end easily, even though we might not see them without looking closer.

One of Excel’s handy tools, the TRIM function, can help fix this problem.

How to Use the TRIM Function in Excel

The Excel TRIM function removes spaces in Excel text. It leaves just one space between words and gets rid of spaces at the beginning and end.

Syntax

=TRIM(text)
  • text – The text from which you want to remove extra space.

Let’s say we have this text in cell B1: ” Hello Excel learner “

To get rid of the multiple spaces between the words we’ll use the TRIM function in cell C1:

Remove spaces in Excel with TRIM

And this is the cleaned up result:

You can also combine the TRIM function directly with your VLOOKUP function:

Combine VLOOKUP with TRIM

This works great… until it doesn’t.

You copy and paste new data into your worksheet and apply the same function’s logic…and it fails.

VLOOKUP not working due to non-breaking spaces

Why didn’t the TRIM function work? Let’s find out the cause of the failure and explore a better solution.

How to Fix Excel TRIM Function Not Working

Why does the TRIM function not remove all spaces for the VLOOKUP function to work? The formula appears to be correct.

=VLOOKUP(TRIM(A9), $G$3:$H$7, 2, FALSE)

If we highlight the TRIM function portions of the formula and press F9, we can clearly see a space at the end of the search value.

Use TRIM to remove spaces in Excel

Why did the TRIM function fail to remove this trailing space?

Although the TRIM function removes spaces in Excel, there are a variety of types of spaces, and TRIM can’t deal with all types.

The TRIM function removes spaces which are generated using the ASCII code of 32.

SIDEBAR: The American Standard Code for Information Interchange (ASCII) code table is a character encoding standard for electronic communication. ASCII codes represent text in computers, telecommunications equipment, and other devices as numbers.  For example, the capital letter “A” is represented in the ASCII table as the number 65, “B” as 66, and “C” as 67, etc.  The “space” character is represented as the number 32.

There is, however, another type of “space” character known as a non-breaking space that is represented by the ASCII code number 160.

How to Use the CODE Function in Excel

If you want to see the code number that hides behind a character, type a character in a cell (like the capital letter “A” in cell A1), then select another cell and write a formula like the following

=CODE(A1)

The CODE function displays the ASCII code for the first character in a string detected in the target cell.

If we were to highlight and copy the trailing space from the value in cell A9 and paste it into our test cell, the CODE function sees it as ASCII code 160.

This non-breaking space is common amongst data that is copied from sources such as email readers, word processors, and web browsers.

How to Use the CHAR Function in Excel

Another Excel text function is the CHAR function.  This function takes an ASCII code number and represents it as its “human-friendly” version.

=CHAR(32) or =CHAR(160)

The above formulas will generate a traditional “space” and a non-breaking “space”.

To our eyes, these both look the same, but Excel sees them as completely different characters, no different than seeing “A” and “B” as different characters.

💡 The TRIM function can remove ASCII 32 characters but not ASCII 160 characters.

Improving the TRIM Function

Until Microsoft releases a version of the TRIM function that can deal with all types of spaces (TRIMALL sounds like a catchy name), we will have to take matters into our own hands and develop a more robust formula that can work with both types of characters.

If we ask ourselves, “How can we get TRIM to see the character 160s as character 32?”  This sounds like the job of a Find/Replace action.

Excel has a function that can replace text with different text: the SUBSTITUTE function.

How to Use the SUBSTITUTE Function in Excel

The SUBSTITUTE function in Excel lets you replace specific text in a cell with new text. For example, if you have a cell with the word “Microsoft” and you want to change “Microsoft” to “XelPlus,” this function can do that for you.

You just tell it what text to look for, what to replace it with, and it does the rest, giving you a new version of the text with the changes you wanted.

=SUBSTITUTE(A1, "Microsoft", "XelPlus")

There is an optional 4th argument that allows you to specify exactly which occurrence in the text to replace, such as “only the 3rd occurrence”.

=SUBSTITUTE(A1, "Microsoft", "XelPlus", 3)

If you omit this 4th argument, all occurrences are replaced.

Updating Our Formula

Remember our original lookup function?

=VLOOKUP(TRIM(A9), $G$3:$H$7, 2, FALSE)

Let’s begin by taking out the TRIM function and putting in its place a SUBSTITUTE function.

=VLOOKUP(SUBSTITUTE(A9, CHAR(160),), $G$3:$H$7, 2, FALSE)

“But wait!  You forgot to replace the non-breaking space with a normal space.”

It’s easy to think that would be the required step, but why replace a non-breaking space with something we ultimately want to throw away?

=VLOOKUP(SUBSTITUTE(A9, CHAR(160), CHAR(32)), $G$3:$H$7, 2, FALSE)

We got a bit crafty here and replaced the non-breaking space with nothing. This smart trick does the job in one step instead of two.

If we fill the updated function down the list, we see that it works in most cases, but fails in a few.

Use SUBSTITUTE to remove non-breaking spaces

This is because the SUBSTITUTE function is not searching for regular spaces to change to nothing.

Our solution is to give the SUBSTITUTE function a “first pass” at extracting non-breaking spaces, but afterward, give the TRIM function a “second pass” to catch any normal spaces.

=VLOOKUP(TRIM(SUBSTITUTE(A9, CHAR(160),)), $G$3:$H$7, 2, FALSE)

Sum Values with Spaces

Suppose we wanted to SUM all the values for “Product A” in our table.

The SUM function returns 0 (zero) because it sees all the data as text.

The below image uses a series of ISTEXT functions to test the “Product A” cells to see if the data is perceived as text by Excel.  As you can see, Excel returns TRUE responses for all the tests.

Another Excel text function called VALUE can read a number stored as text as a ‘normal’ number.

=VALUE(C9)

Although the VALUE function ignores “space” characters, it fails to ignore non-breaking spaces.

If we nested the VALUE function within a TRIM function, it wouldn’t solve our problem.

=TRIM(VALUE(C9))

We need to use the SUBSTITUTE function.

=SUBSTITUTE(C9, CHAR(160),)

But the result is still not a usable number because the original text is still perceived as text.  We need to convert the text to a real number using the VALUE function.

=VALUE(SUBSTITUTE(C9, CHAR(160),))

When to Use

Whenever you encounter data where…

  • your numbers are not being perceived as numbers,
  • you use the TRIM function and it fails,
  • you use the VALUE function and it fails,

…use the SUBSTITUTE function to search for character code number 160 and replace it with “nothing”.

Get Fancy with a Single Step Solution

Instead of creating a pre-processed version of the data to then aggregate, we can perform the conversion and aggregation in a single step.

=SUM(VALUE(SUBSTITUTE(C9:C12, CHAR(160),)))

If you are working with the new Dynamic Array version of Excel, this formula works perfectly.

If you are working with an older (pre-Office 365) version of Excel, you will need to write the formula the same way as before,

=SUM(VALUE(SUBSTITUTE(C9:C12, CHAR(160),)))

..but you will need to commit the formula using the CTRL-Shift-Enter key combination instead of a normal Enter key.

This creates the formula as an array formula to enable the SUBSTITUTE function the ability to process multiple cells worth of data.

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.