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


Relevant Pages

  • Re: Data Validation
    ... Function NoHTML(str As String) As String ... Into a test cell and type this in B1: ... I am putting a string of text in a cell some of which may need to be HTML ... coded for formatting and which must not exceed 30 characters, ...
    (microsoft.public.excel.misc)
  • RE: Generating Passwords in Cells
    ... Special characters 1-0 are okay. ... Dim Cell As Range ... Private Function MakeNewPassword(UserName As String) As String ...
    (microsoft.public.excel.programming)
  • RE: Generating Passwords in Cells
    ... Special characters 1-0 are okay. ... Dim Cell As Range ... Private Function MakeNewPassword(UserName As String) As String ...
    (microsoft.public.excel.programming)
  • Re: Help creating a random string in Perl
    ... can pick one of the 7 characters in my source string) by using "print ... third and fourth characters to and check that the random number hasn't ... shuffling the array ... The idea here is to swap the current cell with some cell ...
    (perl.beginners)
  • Re: word table clear formatting
    ... > cell value cannot be overwritten with table.cell.range.text = ... it is placing the string in front of any characters already ... How did those characters get into the ... >> possible to corrupt the Excel sheet. ...
    (microsoft.public.office.developer.automation)