Text Comparison in Excel

Microsoft Office excel 2007 - upgrade
July 12, 2012 at 04:25:07
Specs: Windows XP, Pentium 4
I need to Compare two cells (having Text ) in Excel and the Out put should be wher it differs ..

Ex: Column A Column B
This is a Text This is & Test


Post compare the Ouput should be " & Test" as these 2 differs from the First Cell .

Please help ..



See More: Text Comparison in Excel

Report •


#1
July 12, 2012 at 11:07:42
There is no built-in Excel function to do what you want directly on the strings. It is either going to require that you split the strings into individual cells and then compare the cells to each other, or it is going to require some VBA code to compare each text string, letter by letter, to see when things change.

If all of your data is similar to your example, then the code could search along, character by character, until the "a" doesn't match the "&" and then return everything after that as being different.

However, if you have anything like this, then things get considerably more difficult, if not impossible:

Tom took the test on Monday.
Mary took the test on Monday.

The only difference is Tom and Mary, but the code is going to see a difference as soon as it checks T against M and spit out that the whole string is different.

In fact, since Mary is one character longer than Tom, none of the characters in those 2 strings will ever match on a position by position basis so the code is going to assume that everything is different.

It would be really hard for the code to only find the difference between Tom and Mary and also determine that everything else is the same.

Perhaps if you posted a few examples of your actual data, we might be able to come up with a different solution.

Please click on the folowing line before posting any example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 12, 2012 at 13:03:04
This does not do all you want,
but it will tell you which cells are Different
and where the first difference occures.

So if your data looks like:

          A                  B
1) This is a Text     This is & Test
2) This is a Text     This is a Text
3) This is a Text     This as & Text

In cell C1 enter the formula:

=IF(EXACT(A1,B1),"Same",MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0))

This is an ARRAY formula and MUST be entered using CTRL-SHIFT-ENTER, else it will not work.

What you get is:

          A                  B           C
1) This is a Text     This is & Test	   9
2) This is a Text     This is a Text	Same
3) This is a Text     This as & Text	   6

The difference in Row 1 begins at character position 9

Row 2 the cells are the same

The difference in Row 3 begins at character position 6, but note that Row 3 has additional differences after the 6th character.

Using DerbDas03's example, you get a 1

So, like I said, it does not do it all, but it gives you something to start with.

MIKE

http://www.skeptic.com/


Report •

#3
July 12, 2012 at 13:27:02
Well, if we are going to assume that the difference is always on the "right hand side" of the string, then this should work to return the actual text difference as requested:

Once again, array entered:

=IF(EXACT(A1,B1),"Same",MID(B1,MATCH(TRUE,MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)<>MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1),0),LEN(B1)))

However, it still won't work for data like:

         A                  B 
1  Mary is a Text     This is a Text

The entire string from B1 will be returned since the MATCH function returns 1.

I guess you could call the entire string in my B1 different from the string in A1 since it starts out differently.

Only the OP can tell us if that works for him.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions


Ask Question