comparing text strings

Posted by carl on March 29, 2001 3:41 PM

I need a macro that will compare two words in adjacent columns and count how many letters the text strings have in common in the same serial position from left to right. For example:

dog dob
dog dbo

The first row has two words (in different columns) that have two letters in common in the same serial position from left to right. The words in the second row also have two letters in common but these are not in the same serial position, in fact they only have one letter in common in the same serial poistion being the first letter d. I would like to run the macro in the third column (currently empty). Any ideas?

Thanks,
Carl.

Posted by cpod on March 29, 2001 5:33 PM

How about this:

=SUM(IF(MID(A1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1)=MID(B1,ROW(INDIRECT("1:"&MIN(LEN(A1),LEN(B1)))),1),1,0))

Posted by Carl on March 30, 2001 2:10 PM

I know how to do this if you're using MS Outlook. It's quite involved so I'll post it if you reply saying Yes, you do use Outlook.

Regards,
Daxum.

Posted by Aladin Akyurek on March 31, 2001 2:47 PM

It's an array formula...

Carl - Something funny with the web-site. You apparently say that cpod's formula does not work. But it does. It's an array-formula, which reminds me of a brainteaser of eons ago. You need to hit CONTROL+SHIFT+ENTER at the same time to enter it.

Aladin

Posted by Carl on April 01, 2001 5:30 PM

Re: It's an array formula... (still not there yet)

Thanks Aladin for your reply, I really appreciate the help, however I still am unable to make the formula work. I'll tell you what I am doing. I have two columns(C), A and B, and a number of rows(R). I am pasting the formula into CR:C1. If A1 contains the word drab, and B1 crab, the formula in C1 returns a value of 0 whereas it should return a value of 3. For comparisons that have the same initial letter the formula returns a value of 1. What am I doing wrong, or what am I missing that cpod and yourself aren't? Many thanks, Carl.

Posted by Carl on April 01, 2001 6:21 PM

Re: It's an array formula... (and its working!!!)...Thanks.

I managed to get it going after all. Many thanks for your help, cpod and aladin.