Formula in Excel to count occurrences of substrings within strings

Finding actual occurrencies via TEXTJOIN function

Using TEXTJOIN (available since version 2019 or MS 365) allows to get the actual number of any occurrencies in one or more columns (column A:A or e.g. even columns A:B).

Thus it avoids the Caveat of the COUNTIF function

"to count only the number of cells (in A:A) that contain the string one or more times. It does not count the total number of instances of the string."

Three Methodical steps

The formula shown (assuming a named search term MySearch) below executes only three logical steps to get the actual number of findings:

• 1 join all filled cells in given column(s) via TEXTJOIN,
• 2 replace the search terms in string 1 with a term length reduced by minus 1 and
• 3 subtract the length of string 2 from length of string 1.

=LEN(TEXTJOIN("|",TRUE,A:B))-LEN(SUBSTITUTE(TEXTJOIN("|",TRUE,A:B),MySearch,LEFT(MySearch,LEN(MySearch)-1)))

MS Help TEXTJOIN function

You are nearly there, use

=COUNTIF(A:A, "*"&B1&"*")

Caveat:
This counts the number of cells in A:A that contain the string one or more times. It does not count the total number of instances of the string.

A very Simple Excel Solution for occurrence of x-string ("ABC") into y-string ("ABC 123 ABC 23dfABC"):

1. Identify length of y-string. Ly = len(y)
2. Substitute x-string by x1-string (append only 1 char of any supported character to x-string. e.g. "ABC"=> "ABC_" ) and calculate length of new string y1-string. Ly1 = len(substitute(y,x,x1)).
3. No of occurrence of x-string) in y-string = Ly1-Ly

Number of Occurrence: len(substitute(y,x,x1)) - len(y)

Nomenclature for Understanding: x-string: "ABC" x1-string: "ABC_" y-string: "ABC 123 ABC 23dfABC" y1-string: ""ABC_ 123 ABC_ 23dfABC_"