Re: Match first few characters in a cell to another

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 03/10/04


Date: Wed, 10 Mar 2004 23:26:13 +0100

Hi Don
maybe this will help you
=IF(LEN(A2)>LEN(SUBSTITUTE(A2,MID(A1,2,LEN(A1)-4),""),"A1 is in A2","no
match")

will skip the first two and the last two characters of A1 for your
comparison. Doing a 'probability' match (counting how many characters
from A1 are within A2) you may use the following array entered formula
(with CTRL+SHIFT+ENTER) to get a percentage:
=SUM(--ISNUMBER(FIND(MID(A1,ROW(INDIRECT("1:" &
LEN(A1))),1),A2)))/LEN(A1)
and format this cell as percentage

Note: this formula does not look if the characters are in order. So the
two strings
QWERTZ
and
ZTREWQ
would get a 100% match (each character of the second string is within
string 1)

--
Regards
Frank Kabel
Frankfurt, Germany
Don wrote:
> Thank you for the reply,
>
> Your solution is not working in every case.  My problem
> is that the string I have in cell A1 may have a couple of
> random characters in the 1st one or two positions and in
> the last few positions.  I need to be able to search the
> string in A2 for the string from character position 2
> thru character position 12 in cell A1 or example. Or get
> a percentage of characters identified or something.
>
> Specifically, I am searching two home addresses that that
> are created from two different sources.  One may add a
> few extra starting or trailing characters but if they
> match they will still have the same basic address inside
> the cell e.g.- "##1600 Pennsylvania ave" and "0001600
> Pennsylvania ave"- 90% of the characters match
> sequentially, this would count as a match.
>
> Thanks; I hope you can help,
>
> Don
>
>
>
>> -----Original Message-----
>> Hi Don
>> one way:
>> =IF(LEN(A2)>LEN(SUBSTITUTE(A2,A1,""),"A1 is in A2","no match")
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>> "Don" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
>> news:9f7d01c40659$75cea730$a401280a@phx.gbl...
>>> Characters X through Y in one cell/string need to be
>>> found in another string of characters in another cell.
>>>
>>> In cell A1 I have "defg"  I need to determine if that
>>> string exists in cell A2 containing "abcdefghijklmnop"
>>>
>>> Thanks for the help,
>>>
>>> Don
>>
>> .