Question:

How do I count the number of times a text string exists in a column? The text string may exist multiple times in a cell.

Answer:

Array formula in cell B11:

How to create an array formula

1. Copy array formula (Ctrl + c)
2. Select cell B11
3. Paste array formula (Ctrl + v) to formula bar
   
4. Press and hold Ctrl + Shift
5. Press Enter
6. Release all keys

You can also use this formula to count how many times a specific character exists in a column in excel.

Explain array formula in cell B11

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

Step 1 - Replace existing text strings with new text string in named range tbl (A1:A6)

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

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

SUBSTITUTE(tbl, $B$9, "")

becomes

SUBSTITUTE({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}, "AA", "")

returns this array:

{"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB  DD"}

Step 2 - Return the number of characters in the named range tbl (A1:A6) without text string "AA"

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))

becomes

SUM(LEN({"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB  DD"}))

and returns 44

Step 3 - Return the number of characters in the named range tbl (A1:A6)

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

SUM(LEN(tbl))

becomes

SUM(LEN({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}))

becomes

SUM({2;9;12;10;9;10})

returns 52.

Step 4 - Return the number of characters in cell B9

(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

LEN($B$9)

becomes

LEN("AA")

returns 2.

Step 5 - All together

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

becomes

=(52-44)/2)

becomes

=8/2

returns 4.

Named ranges

tbl (A1:A6)

What is named ranges?

Download excel example file

count-text-string-in-a-column.xls

(Excel 97-2003 Workbook *.xls)

Functions in this article:

Len(text)
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

Sum(number1,[number2],)
Adds all the numbers in a range of cells

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

• Count number of times a string exist in multiple cells using excel formula
• Count text that occurs multiple times in excel cell
• Count occurences of a specific text string in a column in excel

Read more related articles in the archive.