[Solved] Search multiple strings in one cell

[realmseeker]

Good day,

I am trying to do an if statement that will check for one of several strings in a cell. If string one fails, I want it to check the next string and so on. If any of the strings come back true then do "Blank" otherwise "". I tried the ISERROR command but found it confusing and I got lost in the code.

Code: Select all

=IF(OR(FIND("Shaker";$Input.$B2); FIND("Double Bead";$Input.$B2));" PKDEP="&Input.L2;"")

Thanks folks

Jordan

[gerard24]

Try with an array formula (validate with Ctrl+Shift+Enter instead of Enter) :

Code: Select all

=IF(MAX(ISNUMBER(FIND(criteria_range;$Input.$B2)));" PKDEP="&Input.L2;"")

where 'criteria_range' is the cell range containing your list of strings to be found.

[acknak]

Something like this could work:
=IF(ISERROR(SEARCH("Shaker|Double Bead";$Input.$B2));"";" PKDEP="&Input.L2)

[realmseeker]

Something like this could work:
=IF(ISERROR(SEARCH("Shaker|Double Bead";$Input.$B2));"";" PKDEP="&Input.L2)

Wished this one did . I even tried if quotes around each term individually "shaker" "Double Bead" didn't work.

Gerrard: It didn't work but I think I may not of done it right, so I am going to keep trying on that.

[realmseeker]

Try with an array formula (validate with Ctrl+Shift+Enter instead of Enter) :

Code: Select all

=IF(MAX(ISNUMBER(FIND(criteria_range;$Input.$B2)));" PKDEP="&Input.L2;"")

where 'criteria_range' is the cell range containing your list of strings to be found.

It works Thanks only it doesn't seem to work if I use a cell range from a different sheet. Oh well

[realmseeker]

Try with an array formula (validate with Ctrl+Shift+Enter instead of Enter) :

Code: Select all

=IF(MAX(ISNUMBER(FIND(criteria_range;$Input.$B2)));" PKDEP="&Input.L2;"")

where 'criteria_range' is the cell range containing your list of strings to be found.

It works Thanks only it doesn't seem to work if I use a cell range from a different sheet. Oh well

Okay so I worked with it a bit and it doesn't work sadly.

I am uploading the spread sheet due to the odd behavior of the code Gerrard posted. Pretty much as I drag the "double" and "triple" strings down the page it will blank the columns as it goes.

-Ignore the other sheets only the first two I am working with atm
-Input is where you enter things
-Data processes it for 22MM
-22MM is used to be saved as a CSV for loading into a CAM program
-If either the word "Double" or "Triple" are used in "INPUT.B column" then the related cell on "DATA.F column" should be blank, otherwise display " PKDEP="&INPUT.L(cell#)
-Other terms will be added to this list later
-tried using crtl shift enter sometimes it added { type brackets, sometimes not. it didn't seem to make much difference


Thanks for the help,

Jordan

[acknak]

Something like this could work:
=IF(ISERROR(SEARCH("Shaker|Double Bead";$Input.$B2));"";" PKDEP="&Input.L2)

Wished this one did ...

Sorry. The option Tools > Options > OOo Calc > Calculate > Enable regular expressions in formulas must be on. Your sheet has it turned off. Maybe turning it on will break something else; I haven't checked.

[ken johnson]

gerard24's solution works fine for me.
On the doc you have attached, cell F2 has not been array entered and simply selecting cell F2 then going Ctrl+Shift+Enter will not remedy the situation.
Array formulae can be very unforgiving. If you accidentally neglect to use Ctrl+Shift+Enter, Calc refuses to cooperate when you have a second go at array entering your formula.
To regain Calc's cooperation with an array formula that has previously been incorrectly entered you have to edit the formula first. The simplest method is to select the cell with the failed formula, press the F2 key (that's 'F' as in 'Function key', not 'F' as in 'F2 cell address') to enter edit mode, press the space bar then press Ctrl+Shift+Enter.

After cell F2's array formula has been correctly entered you can use copy/paste to get copies of it into F3 and beyond.
You can also use cell F2's fill handle to achieve the same result. However, since cell F2 has an array formula, Calc insists that the Ctrl key be held down while you drag (or double-click) cell F2's fill handle. If you don't hold down the Ctrl key Calc will punish you by converting your single-valued array formula into a useless multi-valued array formula. Calc will continue the punishment when you try to get rid of the useless multi-valued array formula unless you first select every cell in that array. Ctrl+/ will select all the array's cells after you have selected at least one of them.

Ken Johnson

[realmseeker]

Sorry about not replying sooner. Thanks all for the help. Both solutions work great, and in different places I use one or the other.

Cheers

[heikoschramm]

If you prefer not to use array formulas:
=IF(ISNUMBER(FIND("Shaker";$Input.$B2))=1," PKDEP="&Input.L2,IF(ISNUMBER(FIND("Double Bead";$Input.$B2))=1," PKDEP="&Input.L2,""))

or in more general terms:
=IF(ISNUMBER(FIND("String1",A1))=1,"X",IF(ISNUMBER(FIND("String2",A1))=1,"Y",""))
Where A1 holds the data to be tested, "String1" and "String2" are the terms you are searching for, and "X" and "Y" are the results.