Same Text Contents in Separate Cells gives "FALSE" when compared

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

I wonder if anyone has experince this problem?
I am comparing the following text in excel using
funciton "=", exact, etc

ATLAS GENTECH Atlas Gentech
AXON COMPUTER SYSTEMS LTD Axon Computer Systems Ltd
COMPUTER SOURCE Computer Source
 
Hi

Maybe one or other has trailling/leading spaces? Try
=TRIM(A1)=TRIM(A2)
and see what you get.
 
PK,

Excel doesn't recognise the same text in upper and lower case in the "Exact" function.
"ATLAS GENTECH Atlas Gentech" convert Atlas gentech to uppercase and you will get a true result.

If Atlas Gentech is in cell B1......in C1 type =UPPER(B1) in cell D1 =EXACT(A1,C1) (assuming ATLAS GENTECH is in A1)
should produce a TRUE result.

Harry
 
Thanks, Andy & Harry.

SOrry my 1st mail got sent before I finished it.

Actually I have tried

1. (=A1=A2)
2. Converting those not in CAPS to CAPS using the UPPER
function.
3. Using the TRim function for both cells.
4. FOrmatting both cells to text & comparing again.
5. Copying & pasting "TRIMmed" & "UPPER-ed" results as
text & compare.
6. A combination of all the above.

But got "FALSE"

7. COUNTIF(A1,range B) returns 0
8. vlookup(A1, range B in another table) returns N/A


However, when I delete ALL the spaces between the words in
upper case (F2 to edit) & reinsert them again using the
spacebar, hit enter at the end of the last word, the
formula =A1=A2 returns "TRUE".

I am really baffled?

Right now I need to have got more than 6000 records which
I need to identify any mismatches.
Wonder if u have a better way to do this?
 
Hi

Got it!! The 'spaces' in column A are non-breaking spaces - which are
different to normal spaces! You can do a Find/Replace on them, though, which
will solve your problem. Hit Ctrl H to bring up the Find Replace dialog box.
In the Find box, hold the ALT key and type 0160 on the numberic ley pad
(while holding ALT, that is). It should appear in the box as a space. In the
Replace box just type a space with the spacebar. Problem sorted.
 
THANKS, ANDY!

that was great!

So how does one get "non-breaking" spaces in the first
place? Is it due to the way a certain file (my source file
from downloaded from a databse) is formatted?

Pat
 
Back
Top