Searching for different strings of text

[ellison]

Hi, we found a really helpful formula to search for multiple strings of text... but we are wondering if it could be tweaked to show what actual info was found:

Formula in C2:

=IF(COUNT(SEARCH({"bulk","reel","tube"},B2)),"vocab-review","")

This will search B2 for any of the terms listed (bulk, reel or tube) and return "vocab-review" if it does.

Is there to actually change that into showing which of those terms was actually found?

(Also trying to make this not case dependent, and take into account that Col B often has blanks)

Row	To be reviewed	Helpful Formula for: does it contain "Bulk, tube or reel"?	What We'd Like!
2	bulk packed 10K	vocab-review	bulk
3	3rd party
4
5	brown
6	reels of 5K	vocab-review	reel
7	REEL	vocab-review	reel
8
9	tubes	vocab-review	tube
10

Thanks for any help you can suggest!

 

[Shortmeister1]

What about something like this:

I've added the formatting purely to make it easier to read.

Steps from the inside out:

1. SEARCH - returns a number, if it exists.
2. ISNUMBER - returns true or false.
3. IFS - Each condition specified with a catch all "---" at the end.
   
   

 

[ellison]

Sorry but I'm getting an error message popping up about "You've entered too few arguments"

Here's what I entered into C2:

=IFNA(IFS(ISNUMBER(SEARCH("bulk",$B2)),"bulk",ISNUMBER(SEARCH("reel",$B2)),"reel",ISNUMBER(SEARCH("tube",$B2)),"tube","---")

Have I left a typo in there?

 

[ellison]

mini sheet attached....
I left the equals sign out of C2 so that the sheet would copy to the cliipboard

Book3
	C
2	IFNA(IFS(ISNUMBER(SEARCH("bulk",$B2)),"bulk",ISNUMBER(SEARCH("reel",$B2)),"reel",ISNUMBER(SEARCH("tube",$B2)),"tube","---")
Sheet1

 

[Alex Blakenburg]

You are missing the 2nd last bracket after tube.
,"tube") ,"---")

 

[Fluff]

Just get rid of the IFNA as it's not needed & add a 1 to just before the "---"

=IFS(ISNUMBER(SEARCH("bulk",$B2)),"bulk",ISNUMBER(SEARCH("reel",$B2)),"reel",ISNUMBER(SEARCH("tube",$B2)),"tube",1,"---")

 

[Shortmeister1]

I just pasted my formula and your formula next to each other, mine being above. It's always the brackets! If you look closely, you've left out the closing bracket for ISNUMBER tube. Other than that, it was perfect!

IFNA(IFS(ISNUMBER(SEARCH("bulk",$B2)),"bulk",ISNUMBER(SEARCH("reel",$B2)),"reel",ISNUMBER(SEARCH("tube",$B2)),"tube"),"")

IFNA(IFS(ISNUMBER(SEARCH("bulk",$B2)),"bulk",ISNUMBER(SEARCH("reel",$B2)),"reel",ISNUMBER(SEARCH("tube",$B2)),"tube","")

 

[ellison]

Absolutely brilliant, huge thanks to all who contributed


(And thanks for the tip on the mini sheet Fluff, that makes things a whole lot easier)

 

[Fluff]

@martinshort In future please post your formulae to the board, rather than just an image. It helps to avoid typos & the like.

@ellison Glad we could help & thanks for the feedback.