comparing two text strings in excel to find if they match

Question

I have derived a file using two different methods; FoxPro and SQL. Because I am most familiar with it, I decided to compare the two end results in Excel to see if they are identical. 79,954 rows so needless to say, not going to do this line by line. First, I want to see if the company names are the same. I create a new tab and put the company name columns from each next to each other. From there, The first method that I used is

 =exact (B2,C2)

It returns a false value when I can see with my eyes is not true. Next I use a very simple IF statement,

 =if(b2=c2,0,1)

It returns a value of 1.

Is there a better method of doing this?

Answer 1

EXACT is case sensitive so you need to be sure you don't have any up/low letter mismatch.

You can also use equal which is NOT case sensitive:

=B2=C2

This formula will return TRUE or FALSE.

If it's still not working perhaps you have some spaces or other hidden characters. A solution would be to check if B2 includes C2 and opposite.

Answer 2

=exact (B2,C2) does work, but you might have a space at the end some of your data which will return a false instead of a true because it compares the 2 and sees that "Test " does not equal "Test" because of the space be there.

Answer 3

In addition to hakela's answer, you could TRIM them first:

=(TRIM(B2)=TRIM(C2))