Search for any character contained in a given cell on another cell

Hi guys,

I am trying to write a function that is able to take any character contained in my original cell (which is filled with a text string) and see if it exists on my destination / lookup cell.

So far i made some attempts, one of which included the use of a wildcard (don't know the first thing about wildcards, so i may be using it incorrectly)

It was something of the sort:

=ISNUMBER(SEARCH("["& original cell & "]";within lookup cell)

An example to make my explanation a bit less confuse.

Let's say i have a cell with the text "abcdefg". I want the function to search any of these characters- a, b, c, d, e, f, g - against my lookup cell, which has the text "a". Given that "a" is one of the characters of "abcdefg" the ideal function would return 1. I do not need to know the position of the character, i am only interested in knowing if at least one of the characters exists.

A regular search function returns #VALUE since "abcdefg" is not written anywhere on my lookup cell, which only has "a", thus it cannot be found.

Thanks!

Re: Search for any character contained in a given cell on another cell

Quote from Robert Mika;681722

=SUM(--ISNUMBER(SEARCH({"a","b","c"},A1)))
Confirm Control+Shift+Enter
Add more values separeted by coma.

Hi Robert and thank you for your reply!

I am afraid, however, that i can't do that because the content of my cell varies. I can have "abcdef" or "mothyr" or "a", etc, that's why i was looking for an automatic way of doing it whatever my cell content was.

Best regards

Re: Search for any character contained in a given cell on another cell

Thanks guys!

In the meantime i found an alternative way of doing what i wanted.

Kind regards

Quote from apo;681804

Hi..

This is the first function i have made.. but i think it does the job..

Code

Public Function Dexist(a As Range, b As Range)
Dexist = InStr(a, b)
If Dexist <> 0 Then Dexist = "1"
If Dexist = 0 Or b = "" Then Dexist = ""
End Function