Character Search a cell

[JPS]

In Excel I need to character search a cell and determine if one of 3 text
strings, 01-08, 08-07, 27B can be found anywhere in the cell, if one of them
are found I need UPDATED to show in the results cell, if none of then are
found the results cell will be blank.

 

[Minitman]

Hey JPS;

You could put this into your results cell (you will need to change A1
to your target cell):

=IF(OR(A1="01-08",A1="08-07",A1="27B"),"UPDATED","")

Remember your test data has to be text otherwise the numbers will
compute to something else.

HTH

-Minitman

 

[Stefi]

Say R2 is the cell to be checked, then enter this formula in the result cell:

=IF(NOT(AND(ISERROR(FIND("01-08",R2)),ISERROR(FIND("08-07",R2)),ISERROR(FIND("27B",R2)))),"UPDATED","")

ans drag down as necessary!

Regards,
Stefi


„JPS†ezt írta:

 

[JPS]

I forgot to say that the values that I need to search for will be changing
weekly, so the criteria needs to be flexible.

 

[Stefi]

Put your changing values in cells, say A1,A2,A3 and use this modified formula:

=IF(NOT(AND(ISERROR(FIND(A1,R2)),ISERROR(FIND(A2,R2)),ISERROR(FIND(A3,R2)))),"UPDATED","")

Stefi

„JPS†ezt írta:

 

[Ron Coderre]

With
A1: '01-08
A2: '08-07
A3: '27B

And ...
E1: (text to be tested.....eg "abc 01-08 xyz"...without the quotes)

This formula returns 1 if at least one of the A1:A3 values is in the text:
B1: =MAX(INDEX(COUNTIF(C1,"*"&$A$1:$A$3&"*"),0))

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Rosenfeld]

In Excel I need to character search a cell and determine if one of 3 text
strings, 01-08, 08-07, 27B can be found anywhere in the cell, if one of them
are found I need UPDATED to show in the results cell, if none of then are
found the results cell will be blank.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(rng,C1))),"UPDATED","")

where rng is a range of cells containing the text strings you wish to search
for. C1 is the cell being searched.
--ron

 

[Ron Coderre]

A variation...

=IF(COUNT(INDEX(SEARCH(rng,C1),0)),"UPDATED","")

--------------------------

Best Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[JPS]

I actually used the following and got what I expected Thanks
'=+IF(COUNT(SEARCH($L$1:$N$1,S496)),"UPDATED"," ")