Find all cells that have... (1 Viewer)

[DanG]

I have a sheet that has a list of colleges and some of the college names have "Community" in them or "Comm" after the college name.
I want to mark all colleges that have these designation in them (not all college names are community colleges).

In Access I would use "Like *comm", but not sure in Excel.

Thank you

 

[shades]

I assume by "mark" you mean something like another column that indicates whether that contains the name.

This is more complicated, but it works (Unless there is a college with the name Commander in it). If your names are in column B beginning with cell B2, then in A2, put this (It will return "TRUE" or "FALSE").

=OR(SUM(LEN(B2)-LEN(SUBSTITUTE(B2,"Comm",""))),SUM(LEN(B2)-LEN(SUBSTITUTE(B2,"Community",""))))
________
VOLCANO CLASSIC VAPORIZER

 

[DanG]

Thanks for the help.

Here's another one I found that seems to work very well...

=IF(ISNUMBER(MATCH("*" & "comm" & "*",C6,0)),"Match","")

It's not as simple as Accesss, but I'm not using Access.