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?

hackela · Accepted Answer · 2017-09-13 22:55:15Z

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.

answered Sep 13, 2017 at 22:55

hackela

3931 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Wilcox Over a year ago

I found that there were 18 to 20 extra spaces in the excel sheet that derived from sql. Used a trim function and got reasonable results after that.

Gordon Bell Over a year ago

The SQL column is most likely CHAR or NCHAR then, which retains trailing spaces on export, unlike VARCHAR and NVARCHAR.

DaBeau96 · Accepted Answer · 2017-09-13 22:55:15Z

1

=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.

answered Sep 13, 2017 at 22:55

DaBeau96

5602 gold badges5 silver badges21 bronze badges

Comments

Gordon Bell · Accepted Answer · 2017-09-13 23:08:49Z

0

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

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

answered Sep 13, 2017 at 23:08

Gordon Bell

13.7k3 gold badges48 silver badges65 bronze badges

Collectives™ on Stack Overflow

comparing two text strings in excel to find if they match

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related