Check for Multiple Text Strings in a Cell

[David44357]

This formula will search the B cell for a word and post an indicator if it contains the keyword.
=IF(ISNUMBER(SEARCH("citations",B170)),"1", "")

My obstacle is I need four of these that each search for 3-4 different keywords.

I've tried things like:
=IF(ISNUMBER(OR(SEARCH("citations",B170)), SEARCH("sources",B170))),"1", "")

But I get errors. Does anyone know how to get a formula that checks a cell for multiple keywords? I keep trying to put the OR in different places without success.

-David

 

[Wad Mabbit]

Search for multiple text strings in multiple cells in excel, part 2 | Get Digital Help - Microsoft Excel resource

=IF(COUNT(SEARCH({"citations","source"},B170)),"Yes","")

Hi,
First of all, thanks to all who have contributed to this threat, it's been very helpful. Now, I just need a Rocket Scientist (or Masochist) to help me put some of this on steroids for a book keeping system!

I have data I drop from a bank dump in columns B through to E, Columns F through to H sort Db and Cr and some tagging.
K though M categorise Expenses, M is for Income

I use in-cell drop lists to categorise transactions, and at year end Look at a Profit and Loss with formulas like:
=COA!$H$2 & " " & TEXT(0-
SUMPRODUCT(SUMIFS(
INDIRECT("'"&SheetListSuncorp&"'!d2:d5000"),
INDIRECT("'"&SheetListSuncorp&"'!L2:L5000"),COA!$H$2,
INDIRECT("'"&SheetListSuncorp&"'!K2:K5000"),E_Bus)),
"($#,##0.00)")

Wouldn't it be nice though, if an array could check out the descriptors in each line and assign a category in K? Even L and M where required?

I have this:
B C D G K L M N (income)

09-Mar-16	Upwork Global Inc Dublin IE	-34.74			-34.74		Business	Contractors
09-Mar-16	POST PERTH ST GEOR PERTH AU	-16.95			-16.95		General Living	Other Living
09-Mar-16	BOOST JUICE CARILLON CITYPERTH AU	-14.60			-14.60		General Living	Fast Food
08-Mar-16	Upwork Global Inc Dublin IE	-62.35			-62.35		Business	Contractors
08-Mar-16	BASSENDEAN PHARMACY BASSENDEAN AU	-20.60			-20.60		Business	Medical	Pharmaceutical
07-Mar-16	Upwork Global Inc Dublin IE	-69.54			-69.54		Business	Contractors
07-Mar-16	COLES 0379 MAYLANDS AU	-109.75			-109.75		General Living	Food
06-Mar-16	BUNNINGS 451000 MORLEY AU	-122.79			-122.79		Home	Other (home | maintenance)
06-Mar-16	CP MASSAGE MORLEY AU	-65.00			-65.00		General Living	Medical	Massage
05-Mar-16	HISCO WEST PERTH AU	-26.40			-26.40		-
05-Mar-16	BODY HQ MASSAGE CENT MAYLANDS AU	-70.00			-70.00		General Living	Medical	Massage
05-Mar-16	BODY HQ MASSAGE CENT MAYLANDS AU	-65.00			-65.00		General Living	Medical	Massage
05-Mar-16	HARVEY NORMAN AV/IT WEST PERTH AU	-330.00			-330.00		Home	Equipment
05-Mar-16	HARVEY NORMAN AV/IT WEST PERTH AU	-59.00			-59.00		Home	Equipment
04-Mar-16	TPG INTERNET PTY LTD NORTH RYDE AU	-99.95			-99.95		Business	Office	Telephone | Internet

<tbody>
</tbody>

Note:
- The first row (Upwork) is no 4409
- B4413 contains Bassendean Pharmacy

In K4413 is:
=IF(COUNT(SEARCH(Keyword_ExpenseBusiness,C4413)),"Business",IF(COUNT(SEARCH(Keyword_ExpenseGL,C4413)),"General Living",""))


B4409 results in Business appearing in K4409, which is fine, it is a business item
B4413 results in Business appearing in K4413, which is not fine, it is a General Living item
..so why is this, is it because you cant next an IF in an array? Is the syntax wrong?

Where:

Income Keyword table Mob All Pension Pension Pension Expense | General Living Keyword table Keyword Expense Category Expense Sub-Category Expense Item Chemist General Living PHARMACY General Living Massage General Living Expense | Business Keyword table Keyword Expense Category Expense Sub-Category Expense Item INTERNET Business <tbody> </tbody>
Also, I'm sure some Rocket Scientist can come up with a better idea, as I have tables, eh? I played around with this: =IFERROR(LOOKUP(9.99999999999999E+307,SEARCH(Keyword_ExpenseGL_Table,$B3),OFFSET(Keyword_ExpenseGL_Table,0,1)),"") ...but using an offset could cause problems <tbody> </tbody>

<tbody>
</tbody>

 

[Aladin Akyurek]

@Wad Mabbit

Care to a scaled-down data sample and keyword tables you have, but not discuss any formula? Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl is also a method for posting an exhibit.

 

[Wad Mabbit]

File at http://www.digitalmanagement.net.au/Conundrum.xlsm

 

[Aladin Akyurek]

File at http://www.digitalmanagement.net.au/Conundrum.xlsm

Are you sure you are responding to the request worded in post #22?

 

[Wad Mabbit]

Not exactly, but as my conundrum is similar, I thought it may benefit us both to be in the same post

 

[Davexx]

hi, if anyone still interested, i was looking for a shorter version of what already have. i like item that was posted here for multiple tests in 1 cell (if 1 of the only examples showing that), for
=IF(COUNT(SEARCH({"test1","test2"},DI1585)),1,0) 'for 1 cell only, not case sens, cannot use defined names

i was looking for a shorter version of the following. that answer might be making a user defined function (UDF) to do the same / shorten the name to be something like: SUMF({"test1","test2"},B9:F9)
=IF(SUMPRODUCT(--ISNUMBER(FIND({"test1","test2"},B9:F9)))>0,1,0) multiple strings, range of cells, case sens (must match), defined names: yes, PROBLEM: too long if want to use many times

=IF(SUMPRODUCT(--ISNUMBER(FIND(TOP,CV1581:DC1581)))>0,1,0) defined names, entry has same text (with semi-colons): ={"test1";"test2"} ={"A";"a";2}

EXAMPLE: (name defined name eg: TOP as any name you want)
=SUMPRODUCT(--ISNUMBER(FIND(TOP,J9)))>0
- remote modify cond. formats
- TOP: insert name DEFINE ={2;"A";"a"}
- shortcut: built-in menu copy

 

[Davexx]

Staff: still trying to edit my post. maybe move time up to 3 hours or something.. PS, might be able to swap FIND out with SEARCH, not sure of what different. FIND has worked for me.

 

[Davexx]

hi, if anyone still interested, i was looking for a shorter version of what already have. i like item that was posted here for multiple tests in 1 cell (if 1 of the only examples showing that), for
=IF(COUNT(SEARCH({"test1","test2"},DI1585)),1,0) 'for 1 cell only, not case sens, cannot use defined names

i was looking for a shorter version of the following. that answer might be making a user defined function (UDF) to do the same / shorten the name to be something like: SUMF({"test1","test2"},B9:F9)
=IF(SUMPRODUCT(--ISNUMBER(FIND({"test1","test2"},B9:F9)))>0,1,0) multiple strings, range of cells, case sens (must match), defined names: yes, PROBLEM: too long if want to use many times

=IF(SUMPRODUCT(--ISNUMBER(FIND(TOP,CV1581:DC1581)))>0,1,0) defined names, entry has same text (with semi-colons): ={"test1";"test2"} ={"A";"a";2}


think you can swap: FIND out for: SEARCH not sure of differences.
EXAMPLE: (name defined name eg: TOP as any name you want)

=SUMPRODUCT(--ISNUMBER(FIND(TOP,J9)))>0
- remote modify cond. formats
- TOP: insert name DEFINE ={2;"A";"a"}
- shortcut: built-in menu copy