Match first few characters in a cell to another

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

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
 
Hi Don
If you don't want 2 blank cells to give you a positive
match, you can also use:

=IF(ISBLANK(A1:B1),"No Match",IF(ISERROR(FIND
(A1,B1,1),"","YES"
yOU WILL HAVE TO CTRL+SHIFT+ENTER AFTER TYPING IN THE
FORMULA.

Regards
Michael
 
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
 
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)
 
Back
Top