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

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

• 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

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

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

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

Could you post an example in workbook?

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

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``````
• 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``````

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!