# Number of letters in common in different cells

Posted by Carl W on May 15, 2001 10:02 PM

If I have a word in A1 and a word in B1 what logic string will produce the number of letters in common regardless of letter position in word. I would like this value to be displayed in C1.

Many Thanks,
Carl.

Posted by Aladin Akyurek on May 15, 2001 11:26 PM

Carl

In C1 array-enter: =SUM(1*ISNUMBER(SEARCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),B1)))

In order to array-enter a formula, you need to hit CONTROL+SHIFT+ENTER at the same time (instead of just ENTER).

Copy down the formula as far as needed.

Posted by Carl W on May 16, 2001 1:07 AM

Works like a dream. Whats the deal with array formulas as opposed to regular formulas?

Carl

Posted by Aladin Akyurek on May 16, 2001 9:40 AM

Caution

Carl,

The formula will flounder on some specific pairs of words.

Working on it...

Posted by Mark W. on May 16, 2001 1:22 PM

Re: Caution

=SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(\$65:\$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW(\$65:\$90)),1))

There may be a simpler approach, but I've grown
quite fond of FREQUENCY(). : )

Posted by Aladin Akyurek on May 16, 2001 1:46 PM

Re: Caution

Carl -- Mark's FREQUENCY formula is just what we need. Thanks Mark. , this will work... =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(\$65:\$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW(\$65:\$90)),1)) There may be a simpler approach, but I've grown

Posted by Mark W. on May 16, 2001 2:46 PM

Even more cautious

Aladin, this modification will guard against the
encroachment of special characters (one can't be
too careful), but must be entered as an array
formula...

{=SUM(IF(FREQUENCY(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(\$64:\$90))*FREQUENCY(CODE(MID(UPPER(B2),ROW(INDIRECT("1:"&LEN(B2))),1)),ROW(\$64:\$90))*MID(0&REPT(1,26)&0,ROW(\$1:\$28),1),1))} -- Mark's FREQUENCY formula is just what we need. Thanks Mark. , this will work... : =SUM(IF(FREQUENCY(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(\$65:\$90))*FREQUENCY(CODE(MID(UPPER(B1),ROW(INDIRECT("1:"&LEN(B1))),1)),ROW(\$65:\$90)),1)) : There may be a simpler approach, but I've grown

Posted by Carl W on May 17, 2001 8:19 PM

Re: Even more cautious (thanks again)