Question: How do I count the number of times a text string exists in a column?

Answer:

text-string-exists-in-a-column

Formula in B11:

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9) + CTRL + SHIFT + ENTER

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

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.

Read more related articles in the archive.

  • Share/Bookmark

Related posts:

  1. Count text that occurs multiple times in excel cell
  2. Search for a text string in an excel table
  3. Count unique distinct text values in a range in excel
  4. Sum adjacent values using multiple lookup text values in a column in excel
  5. Filter unique distinct values where adjacent cells contain search string in excel
  6. Unique list to be created from a column where an adjacent column has text cell values
  7. Count unique distinct values in a column in excel
  8. Count unique text values in a range containing both numerical and text values
  9. Count duplicate distinct values in a column in excel