SEARCH but for cell contents rather than text

[Matt D Francis]

OK so I want to use the SEARCH function but to look for the contents of one
cell in another. Example

|A|B|C|
1|orange|an orange|(my formula)
2|apple|an orange|(my formula)
3|pear|a pear|(my formula)

So I want to put a formula in C1 that searches B1 for any occurance of the
text that appears in A1 and return the position (like CHARINDEX in SQL)

So
|A|B|C|
1|orange|an orange|3
2|apple|an orange|#VALUE
3|pear|pear|1

I can't 'hardcode' my search value as it's changing each row. Whats the
secret?

 

[Mike H]

Mat,

I you happy to see a value error if the string doesn't exist then use this

=FIND(A1,B1)

if you want to eliminate the value error

=IF(ISERROR(FIND(A1,B1)),"",FIND(A1,B1))

Mike

 

[Mike H]

forgot to mention my first version is case sensitive, this isn't

=IF(ISERROR(SEARCH(A1,B1)),"",SEARCH(A1,B1))

Mike