FIND with multiple criteria

[batkosta]

Dear experts,

I have the following formula:

=FIND("(",Sheet1!$A12,1)

What I would like is to include "[" and "/" as the FIND criteria as well.

I tried

=FIND({"(","[","/"},Sheet1!$A12,1)

with no luck.

I understand that excel will get confused if at least two of the criteria are present in the string but this is not the case in my data.

Thanks in advance.


Zealot

 

[jindon]

How about

=Sum(CountIf(sheet1!A12,{"*(*","*[*"}))>0

 

[batkosta]

Thanks jindon, your formula works great but I forgot to mention that I still need the position of the first found character within the string (since I'll be deleting everything after that character). Thought there might be an easy workaround for this but it looks like a vba thing.


Thanks,

Zealot

 

[jindon]

Can you show me a few sample with before/after?

 

[Richard Schollar]

Hi Zealot

You mean like this:

Excel Workbook
	A
1	Hello my name is Richard / and your name is?
2
3	Hello my name is Richard
Sheet1

 

[batkosta]

Precisely. Many thanks to both of you.


Zealot