Count how many times a substring occurs in a column

[Andrew Entee]

Hi everyone. I'm trying to determine how many times the word "MESA" occurs in a column of text messages (but only count ONE occurrence per row if it is there more than once in a cell). I can't quite get the formula right. I think I need to use COUNTIF and FIND - but doesn't seem to work. Here is the column of text. I can't attached the xls because of confidentiality, but this is column H:

ALARMS - RES - ALARMS - SF RESIDENTIAL
MESA-D - MESA-D
WIRES DOWN - WIRES DOWN
MESA-C - MESA-C

MESA-B - MESA-B
ALARMS - RES - ALARMS - SF RESIDENTIAL
MESA - MESA
MESA-D - MESA-D
MESA-C - MESA-C
WIRES DOWN - WIRES DOWN
MESA-C - MESA-C
MESA-D - MESA-D
ALARMS - RES - ALARMS - SF RESIDENTIAL
MESA - MESA
MESA-D - MESA-D
MESA-D - MESA-D
MESA-D - MESA-D
MESA-D - MESA-D
BURNING COMP - BURNING COMPLAINT
BURNING COMP - BURNING COMPLAINT
ALARMS - NON-RES - ALARMS RINGING - MR / COMMERCI
MESA-D - MESA-D
MESA-D - MESA-D
MESA-C - MESA-C
ALARMS - RES - ALARMS - SF RESIDENTIAL
ALARMS - RES - ALARMS - SF RESIDENTIAL
MESA-D - MESA-D
MISC/Other - MISC/OTHER/SPECIAL/NOT LISTED
BURNING COMP - BURNING COMPLAINT
MESA-C - MESA-C
MVA - MVA
MESA-D - MESA-D
LIFT ASSIST - LIFT ASSIST
BURNING COMP - BURNING COMPLAINT
BURNING COMP - BURNING COMPLAINT
MESA-B - MESA-B
MESA-D - MESA-D
MESA-C - MESA-C
MESA-D - MESA-D
ALARMS - RES - ALARMS - SF RESIDENTIAL
MISC/Other - MISC/OTHER/SPECIAL/NOT LISTED
BURNING COMP - BURNING COMPLAINT
MESA-D - MESA-D
BRUSH FIRE - BRUSH/GRASS FIRE
RESCUE - OTHER - RESCUE - OTHER
MISC/Other - MISC/OTHER/SPECIAL/NOT LISTED
NG LEAK/SMELL - NATURAL GAS LEAK/SMELL
MESA-D - MESA-D
ALARMS - RES - ALARMS - SF RESIDENTIAL

CO ALARMS - CARBON MONOXIDE ALARMS
STR FIRE - POSSIBLE - STRUCTURE FIRE - POSSIBLE
STR FIRE - POSSIBLE - STRUCTURE FIRE - POSSIBLE
MESA-D - MESA-D

MESA-D - MESA-D
ALARMS - RES - ALARMS - SF RESIDENTIAL
MISC/Other - MISC/OTHER/SPECIAL/NOT LISTED
MESA-D - MESA-D
MESA-A - MESA-A
BURNING COMP - BURNING COMPLAINT
BURNING COMP - BURNING COMPLAINT
ALARMS - RES - ALARMS - SF RESIDENTIAL

[jeffreybrown]

It seems this should work >> =COUNTIF(H1,"*Mesa*")

What result do you expect? I get a sum of 29

[HansDouwe]

This returns 29 also: =SUM(N(ISNUMBER(FIND("MESA",H1:H99))))

[Andrew Entee]

Thanks very much for your help Hans and Jeff. Both of these works well - I didn't realize that "*" could be used - you taught me something! Thanks!

[jeffreybrown]

You are very welcome. Glad we could help!

[HansDouwe]

You are Welcome!

Thanks for the feedback and rep . Glad to have helped.