Not sure I can get my TRIM formula to work or if it's something else

[matt1020]

Hi Everyone,
The mystery today is: Why does a TRIM formula work sometimes and not others? I attached a spreadsheet if anyone wants to look at it if they're bored with tough questions.

[Special-K]

If your data has come from the web it could include CHAR(160) which looks like a space.
TRIM does not remove this character.
You'll prob have to put the result in a SUBSTITUTE(A1,CHAR(160),"") or similar

[Special-K]

E16 is a number
D16, ie TRIM(E16) is text
You need to convert to numbers by using

TRIM(E16)+0

Do this and they'll all show up

[shirleyxls]

I changed column A and Column D to "Text" format and hey presto, it returns the Description.

It's probably because Excel tries to work out if something is a number if you just have General format so it perhaps couldn't make the match.

Give it a try on your version and post again if you found other instances.


Excel is a constant learning process and it's great to help each other. If I've helped you today, a click on the star on the left is appreciated.

[shirleyxls]

SpecialK is right about the numbers, but some of your codes are combinations of letters and numbers so I guess you want them all as text.

[MrShorty]

Here's Microsoft's documentation for this issue: https://support.office.com/en-us/art...c-fe5303222c9d As mentioned above, the TRIM() and CLEAN() functions only remove certain subsets of the entire list of "non-printing" characters.

[Special-K]

Do this and they'll all show up

Sorry, I didnt look that closely, am at work and it's near home time (!!)

[AliGW]

Your formula is unnecessarily long (too many sets of parentheses) and can be done like this instead:

=IFERROR(IF(A6<1,"",INDEX($G$5:$G$25,MATCH(A6,$D$5:$D$25,0))),"No Definition")

I can't see what the problem is, but it clearly doesn't like the result of the TRIM formula. Perhaps if you create a macro to get the unwanted spaces out of the original item number column?

[FDibbins]

Try this work-around...
=IFERROR(IF(A5<1,"",INDEX($G$5:$G$25,IFERROR(MATCH(A5,$E$5:$E$25,0),MATCH("*"&A5&"*",$E$5:$E$25,0))))," No Definition")

[FDibbins]

Glad that worked for you and thanks for the feedback