finding occurance of characters

E

easwara

Hi,

I have the data like the following.

ABAACADAM

I would like to find whther the letter "B" is present in it or not. So,
I used FIND("B",F25) which returned me 2.

but, my objective is to find "B","C","D","W". say for eg, if B is not
present then the letter "C" should be searched and the
occurence/position has to be returned. The order of precedence should
be maintained for the letters to be searched. i.e., if B is not found,
search for C, if not found, search for D and if not found search for W.
if nothing is present I should place 0(zero) in the resultant cell.

Is there any simple solution for this task?

thanks
Easwara
 
B

Bob Phillips

=MIN(IF(MID(A21,ROW(INDIRECT("1:"&LEN(A21))),1)={"B","C","D","W".},ROW(INDIR
ECT("1:"&LEN(A21)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Max

Another play to try ..

Put in say, G25:
=IF(ISERROR(FIND("B",F25)),IF(ISERROR(FIND("C",F25)),IF(ISERROR(FIND("D",F25
)),IF(ISERROR(FIND("W",F25)),0,FIND("W",F25)),FIND("D",F25)),FIND("C",F25)),
FIND("B",F25))
 
M

Max

Thanks Bob & Max!! Both solutions works!! :)
You guys Rock!!
Click to expand...

You're welcome ! Thanks for the feedback

But .. there might be some subtle differences <g>
... is the order of precedence check still important?,
re your:
... The order of precedence should
be maintained for the letters to be searched.
i.e., if B is not found, search for C, if not found,
search for D and if not found search for W.
if nothing is present .. place 0 (zero) in the resultant cell.
Click to expand...

I tested with, say the data: AWAACADAM
Bob's returns: 2 ("W"),
while mine returns: 5 ("C")

With say: AADAMBMM
Bob's returns: 3 ("D"),
while mine returns: 6 ("B")
 
Top