Text Compare Function

[Guest]

I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC.
Is anyone familiar with this function? Thanks in advance.

 

[Guest]

A few more examples of what you consider to be matches would be helpful.

***********
Regards,
Ron

XL2002, WinXP

 

[Ron Rosenfeld]

I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC.
Is anyone familiar with this function? Thanks in advance.

You could use the FIND function to determine if the shorter text string is
contained within the longer text string.

Is that what you want?


--ron

 

[Guest]

Thanks Ron. Somethings like:
LAC vs. 2LAC
FULT vs. FULTUS
DB vs. DBK
Thinks like that?

 

[Guest]

Here are a couple options.....

For a value in A1 to find in B1

This formula matches TEXT entries in B1 (12ABC, xyz, etc):
C1: =COUNTIF(B1,"*"&A1&"*")>0

OR
This formula matches any kind of entry in B1 (56, abc, a99b, etc)
C1: =ISNUMBER(SEARCH(A1,B1))

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thank you Ron. I tried this, but it basically indicated a match if ANY
letter was in common with the two cells. I'd like to see if the text string
in A1
matches to any part of the text string in B1. Which is why I want ABC &
2ABC to be a match, and ABC & ABCUS to be a match. However, I would not
want "ABC" and "A Contracting Services" to match. Basically, MATCH only if
the entire text string from A1 matches some part of B1.

 

[Guest]

Jim

Perhaps I'm missing something....
Here's a table of values and results using the various techniques:

Where:
COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")>0
SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1))
FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1))

Col_A Col_B COUNTIF SEARCH FIND
lac 2LAC TRUE TRUE FALSE
FULT FULTUS TRUE TRUE TRUE
DB DBK TRUE TRUE TRUE
LAC LAWK FALSE FALSE FALSE
FULT 3FULAT FALSE FALSE FALSE
DB FALSE FALSE FALSE

Which of those returned values is incorrect?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron, I think this is getting me there. Thank you so much. The COUTNIF and
SEARCH functions seem to net the same (and correct) results. What is the
difference between the two?
The only problem I see is that if I had just the letter A in column A and
then ABC in column B, then the formulas would indicate a match. But I don't
think there's a way to protect against that.

 

[Guest]

Jim

The difference between the COUNTIF and the SEARCH functions, in this case, is:
The COUNTIF function won't find a proper match if the Col_B value is a number.
The SEARCH function handles that case correctly

Example:
A1: 56
B1: 567

The COUNTIF version returns FALSE.
The SEARCH version returns TRUE.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron, you have been immensley helpful. Thanks very much for your time.
You've solved my problem for me.

 

[Guest]

You're very welcome.
Thanks for the feedback; I'm glad that worked for you.
***********
Regards,
Ron

XL2002, WinXP