SEARCH but for cell contents rather than text

M

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?
 
M

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
 
M

Mike H

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

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

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top