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)
[TABLE="width: 1100"]
<tbody>[TR]
[TD="align: right"]09-Mar-16[/TD]
[TD]Upwork Global Inc Dublin IE[/TD]
[TD="align: right"]-34.74[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-34.74[/TD]
[TD][/TD]
[TD]Business[/TD]
[TD]Contractors[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09-Mar-16[/TD]
[TD]POST PERTH ST GEOR PERTH AU[/TD]
[TD="align: right"]-16.95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-16.95[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Other Living[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09-Mar-16[/TD]
[TD]BOOST JUICE CARILLON CITYPERTH AU[/TD]
[TD="align: right"]-14.60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-14.60[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Fast Food[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar-16[/TD]
[TD]Upwork Global Inc Dublin IE[/TD]
[TD="align: right"]-62.35[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-62.35[/TD]
[TD][/TD]
[TD]Business[/TD]
[TD]Contractors[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar-16[/TD]
[TD]BASSENDEAN PHARMACY BASSENDEAN AU[/TD]
[TD="align: right"]-20.60[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-20.60[/TD]
[TD][/TD]
[TD]Business[/TD]
[TD]Medical[/TD]
[TD]Pharmaceutical[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar-16[/TD]
[TD]Upwork Global Inc Dublin IE[/TD]
[TD="align: right"]-69.54[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-69.54[/TD]
[TD][/TD]
[TD]Business[/TD]
[TD]Contractors[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar-16[/TD]
[TD]COLES 0379 MAYLANDS AU[/TD]
[TD="align: right"]-109.75[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-109.75[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Food[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Mar-16[/TD]
[TD]BUNNINGS 451000 MORLEY AU[/TD]
[TD="align: right"]-122.79[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-122.79[/TD]
[TD][/TD]
[TD]Home[/TD]
[TD="colspan: 2"]Other (home | maintenance)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Mar-16[/TD]
[TD]CP MASSAGE MORLEY AU[/TD]
[TD="align: right"]-65.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-65.00[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Medical[/TD]
[TD]Massage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-16[/TD]
[TD]HISCO WEST PERTH AU[/TD]
[TD="align: right"]-26.40[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-26.40[/TD]
[TD][/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-16[/TD]
[TD]BODY HQ MASSAGE CENT MAYLANDS AU[/TD]
[TD="align: right"]-70.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-70.00[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Medical[/TD]
[TD]Massage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-16[/TD]
[TD]BODY HQ MASSAGE CENT MAYLANDS AU[/TD]
[TD="align: right"]-65.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-65.00[/TD]
[TD][/TD]
[TD]General Living[/TD]
[TD]Medical[/TD]
[TD]Massage[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-16[/TD]
[TD]HARVEY NORMAN AV/IT WEST PERTH AU[/TD]
[TD="align: right"]-330.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-330.00[/TD]
[TD][/TD]
[TD]Home[/TD]
[TD]Equipment[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar-16[/TD]
[TD]HARVEY NORMAN AV/IT WEST PERTH AU[/TD]
[TD="align: right"]-59.00[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-59.00[/TD]
[TD][/TD]
[TD]Home[/TD]
[TD]Equipment[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar-16[/TD]
[TD]TPG INTERNET PTY LTD NORTH RYDE AU[/TD]
[TD="align: right"]-99.95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]-99.95[/TD]
[TD][/TD]
[TD]Business[/TD]
[TD]Office[/TD]
[TD]Telephone | Internet[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 524"]
<tbody>[TR]
[TD][TABLE="width: 524"]
<tbody>[TR]
[TD="colspan: 2"]Income Keyword table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mob All[/TD]
[TD]Pension[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pension[/TD]
[TD]Pension[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Expense | General Living Keyword table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keyword[/TD]
[TD]Expense Category[/TD]
[TD]Expense Sub-Category[/TD]
[TD]Expense Item[/TD]
[/TR]
[TR]
[TD]Chemist[/TD]
[TD]General Living[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PHARMACY[/TD]
[TD]General Living[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Massage[/TD]
[TD]General Living[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Expense | Business Keyword table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Keyword[/TD]
[TD]Expense Category[/TD]
[TD]Expense Sub-Category[/TD]
[TD]Expense Item[/TD]
[/TR]
[TR]
[TD]INTERNET[/TD]
[TD]Business[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 524"]
<tbody>[TR]
[TD]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
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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