Find if column A string is a substring of column B items

[travelfreakg2g]

Hello All

I am trying compare a & b.
I want to compare column a items with column b items.

But, I want a function which could check if column b item is contained in column a.
For e.g.

Column a. レタスコ　　
Column b. レタス

Column a has レタス but also コ. But, column b has only レタス.
So, now I want the column a to change to レタス, and コ should be omitted.

Column a & b has different sized strings so i can`t use index functions.

Is there a way we can compare each letter in column a with letter in column b.

[Glenn Kennedy]

You cannot doexactly that with a formula... but see if this is close enough:


=IF(A2="","",IF(ISNUMBER(SEARCH(B2,A2)),B2,"Not present"))

[travelfreakg2g]

Thank you Glenn.

This seems to be working but could you modify it a little.
What if we want to compare items in column a to items in column b of the table.

So, the function will check first item in column a with all the items in column b and return the result present or not present.
Then, second item in column a and so on.

[Glenn Kennedy]

Maybe this (I've jumbled them up a bit):

=IF(B2="","",IF(SUMPRODUCT(--NOT(ISERR(SEARCH(B2,$A$2:$A$10))))>0,B2,"Not present"))

[travelfreakg2g]

Thank you Glenn. This is perfect.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.