Comparing Values in 2 columns

[gaby58]

Hi all

I am trying to compare values in 2 columns and the values are same but it is retuning false
Tried Trim function, still doesn't work, any idea how to get this one working, appreciate your help

Thank You

[xenixman]

Try the CLEAN() function or use both CLEAN & TRIM

=CLEAN(TRIM(A1))

[Glenn Kennedy]

That doesn't work for CHAR 160, I think...

=SUBSTITUTE(clean(A1),CHAR(160),"")

Failing that...Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[xenixman]

It also does not work for CHAR 127 so try this also:

=SUBSTITUTE(A1,CHAR(127),"")

[jtakw]

Hi,

Since CHAR 127 is "Visible" and OP would've noticed it, I tend to doubt that's the culprit, it's most likely CHAR 160...

Depending on OP's data, there're at least a couple of ways to "clean up" the data, so a sample would help.

[xenixman]

You are, of course, correct,
CHAR 127 displays as a small square with a question mark inside,
but since it is the only other character that CLEAN() does not work on, figured it was worth the mention.

[xenixman]

Another way to display the Non-printable characters is to switch your Font to Terminal
CHAR 160 will now display as lowers case A with an accent mark
CHAR 127 will now display as a kind of triangle

[xenixman]

Just noticed that Win XP and Win 7 display the CHAR 127 character differently.

Win XP as a box with a question mark
Win 7 as a perpendicular bar