Trim Function not Working in Excel 2013

[maribeth721]

I have about 400k cells in a single column that have trailing spaces at the end, which is causing my VLOOKUP not to work. I am trying to remove the extra spaces using the TRIM function. However when I do this, nothing happens. The cell just remains looking like =TRIM(A2) with no corrected data in it. What am I doing wrong?

[AlKey]

Try this formula

=SUBSTITUTE(A2,CHAR(160),"")

[shg]

Change the format of the cell in which the formula appears to something OTHER than Text or General, e.g., Number

[Jonmo1]

Change the format of the cell in which the formula appears to something OTHER than Text or General, e.g., Number

AND, RE-Enter the formula.

[Tony Valko]

Instead of using 400k formulas to get rid of that character try running the macro at this link:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

[Ron Coderre]

I suspect that the trailing "space" is actually an HTML-non-breaking-space.
Try this on one of the cells...
Example:
=CODE(RIGHT(A1,1))

If it returns 160...it's an HTML NBSP

You might need to use something like this:

Please Login or Register  to view this content.

Alternatively, you might try Find/Replace
• Select the range
• Home.Find&Select.Replace
...Find what: Hold down ALT...Type: 0160 (on the number keypad)...Release ALT
...Replace with: (leave this blank)
...Click: Replace all


Is that something you can work with?

[maribeth721]

This did the trick. Thank you!

[shg]

We'll never know ...

[ApacheCleric]

shg - we will never know which one SHE used that worked, BUT changed the type from text to date (even though it only contained text) and then retyping in the formula worked for me.

[ifdinc]

I had the NBSP issue. The item was originally in Word and I pasted it in Excel. Ron's advice was spot on.