Character Search a cell
 

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.
--
JPS

Character Search a cell
 

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

On Thu, 13 Mar 2008 01:03:01 -0700, JPS
wrote:

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.

Character Search a cell
 

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:

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.
--
JPS

Character Search a cell
 

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


"Stefi" wrote:

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:

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.
--
JPS

Character Search a cell
 

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,R 2)),ISERROR(FIND(A3,R2)))),"UPDATED","")

Stefi

€žJPS€� ezt Ã*rta:

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


"Stefi" wrote:

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:

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.
--
JPS

Character Search a cell
 

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)

"JPS" wrote in message
...
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.
--
JPS

Character Search a cell
 

On Thu, 13 Mar 2008 01:03:01 -0700, JPS wrote:

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

Character Search a cell
 

A variation...

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

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

Best Regards,

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

"Ron Rosenfeld" wrote in message
...
On Thu, 13 Mar 2008 01:03:01 -0700, JPS
wrote:

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

Character Search a cell
 

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


"Ron Coderre" wrote:

A variation...

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

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

Best Regards,

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

"Ron Rosenfeld" wrote in message
...
On Thu, 13 Mar 2008 01:03:01 -0700, JPS
wrote:

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