Find cells which start (not contain) search strings

[ellison]

Hi, we have some excellent code which allows us to search for if cells "contain" various search strings.

We are wondering if it would be possible to tweak it so that it would only show results where the cell "starts with" as opposed to contains.

(And hopefully deal with upper or lower case & blank cells)

Finding-Vocab-In-Cells-contains-OR-starts.xlsx
	A	B	C	D
1	Row	Info	Result Of Current Formula	Goal of a new formula which only does "begins with"
2	2	alpha	vocab-review	vocab-review
3	3	Not alpha	vocab-review
4	4	charlie	vocab-review	vocab-review
5	5	No longer charlie	vocab-review
6	6	delta
7	7
8	8	ECHO	vocab-review	vocab-review
9	9	_echo	vocab-review
Sheet2

Cell Formulas
Range	Formula
C2:C9	C2	=IF(COUNT(SEARCH({"alpha","beta","charlie","echo"},B2)),"vocab-review","")

Any help hugely appreciated!

 

[Alex Blakenburg]

I am sure someone will come up with a better option but this seems to work.

=IF(SUM(COUNTIFS(B2,{"alpha","beta","charlie","echo"} & "*"))=1,"vocab_review","")

 

[ellison]

Absolutely amazing, massive thanks!!!!

 

[AlanY]

same idea

=IF(SUM(COUNTIFS(B2,{"alpha","beta","charlie","echo"})),"vocab_review","")

 

[Alex Blakenburg]

same idea

=IF(SUM(COUNTIFS(B2,{"alpha","beta","charlie","echo"})),"vocab_review","")

While that works for the examples given it doesn't cover of

only show results where the cell "starts with"

 

[Alex Blakenburg]

Absolutely amazing, massive thanks!!!!

Glad we could help.