IF statement locating text string (2002 SP-2)

Topic

New Reply

Is there a way to create an IF statement to locate a string of text?

I’ve tried ^=IF(DB2 =*offender*,”Offender”,IF(DB2 =* arrest*,”Arrest “, IF(DB2=*assault*,”Assault”,IF(DB2 =*Restrain*,”Restrain”,IF(DB2 =*dog*,”Dog”,””)))) and excel doesn’t seem to like the *’s.

I have a comment field and I want to be able to identify certain situations and by isolating only those comments that contain the above mentioned words. I thought that by inserting the above formula, I could then pivot the results to get just the items I want.

I’ve tried searching excel help to no avail and only found
^=IF(ISERR(SEARCH(“solo”,D1)),1,0)
on this site, which doesn’t really support the multiple words I am looking for.

Does anyone have any suggestions as to what I might try?

Thanks

Reply | Quote

Replies

Hi Capri,

You can test for the presence of one of a number of strings within another like this:
=IF(NOT(ISERROR(FIND(“offender”,DB2))),”Offender”,IF(NOT(ISERROR(FIND(“arrest”,DB2))),”Arrest”,IF(NOT(ISERROR(FIND(“restrain”,DB2))),”Restrain”,IF(NOT(ISERROR(FIND(“dog”,DB2))),”Dog”,””))))
Note: the FIND function isn’t case sensitive – use the SEARCH function if you need a case-sensitive test.

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks

However I tried your formula and every cell even those containing the words return blanks.
I don’t want case sensitive as the comments could be any combination of upper and lower case.

Reply | Quote

Hi capri,

The formula works as expected for me, using cell DB2 as the test cell. If any of those strings appears, the first one found by the order in which they appear in the IF test is returned, otherwise the result is blank.

Are you sure you’ve got the correct cell references?

Cheers

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Did you try the function replacing FIND with SEARCH? FIND actually is case sensitive, whereas SEARCH is not.

Reply | Quote

[indent]

---

Note: the FIND function isn’t case sensitive – use the SEARCH function if you need a case-sensitive test.

---

[/indent]
It’s the other way round – FIND is case sensitive, SEARCH isn’t.

Reply | Quote

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote