Article updated on January 12, 2018

Question: How do I count how many times a text string occurs in a cell in excel?



Formula in B5:

=(LEN(B1)-LEN(SUBSTITUTE(B1, B3, "")))/LEN(B3)

Recommended articles

Count text strings in formulas [VBA]

Rahul Jadhav asks: I have many excel files with multiple sheets and each excel sheet has many formula which are […]

Substitute multiple text strings [UDF]

The user defined function demonstrated below replaces multiple old text strings with new text strings in a cell. The custom […]

Extract numbers from a value

Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in […]

Functions in this article:

Returns the number of characters in a text string

Substitute(text, old_text, new_text, [instance_num])
Replaces existing text with new text in a text string

This blog article is one out of three articles on the same subject.

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Count text string in a range (case sensitive)

Question: How do I count the number of times a text string exists in a column? The text string may […]