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.
The SUBSTITUTE Function
The SUBSTITUTE function will search a cell for a defined text string and replace it with another text string. An example would be as follows:
=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, which is a legal move and more efficient because it achieves what would have been a 2-step process in a single step.
If we fill the updated function down the list, we see that it works in most cases, but fails in a few.
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)