Character Search a cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JPS - 13 Mar 2008 09:03 GMT

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.

Signature

JPS

Reply to this Message

Minitman - 13 Mar 2008 09:49 GMT

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

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

Reply to this Message

Stefi - 13 Mar 2008 10:06 GMT

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.

Reply to this Message

JPS - 13 Mar 2008 14:55 GMT

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

Signature

JPS

> Say R2 is the cell to be checked, then enter this formula in the result cell:
>
[quoted text clipped - 11 lines]
> > are found I need UPDATED to show in the results cell, if none of then are
> > found the results cell will be blank.

Reply to this Message

Stefi - 13 Mar 2008 15:04 GMT

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:

> I forgot to say that the values that I need to search for will be changing
> weekly, so the criteria needs to be flexible.
[quoted text clipped - 14 lines]
> > > are found I need UPDATED to show in the results cell, if none of then are
> > > found the results cell will be blank.

Reply to this Message

Ron Coderre - 13 Mar 2008 15:18 GMT

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)

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

Reply to this Message

Ron Rosenfeld - 13 Mar 2008 17:46 GMT

=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

Reply to this Message

Ron Coderre - 13 Mar 2008 17:59 GMT

A variation...

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

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

Best Regards,

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

>>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
[quoted text clipped - 8 lines]
> for. C1 is the cell being searched.
> --ron

Reply to this Message

JPS - 17 Mar 2008 09:21 GMT

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

Signature

JPS

> A variation...
>
[quoted text clipped - 20 lines]
> > for. C1 is the cell being searched.
> > --ron

Reply to this Message