Tag: LEN function

How to count specific words in a range in Excel

by

To count how many times a specific a word (or any substring) appears inside a range of cells, you can use a formula based on the SUBSTITUTE, LEN, and SUMPRODUCT functions.  Formula =SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,text,””)))/LEN(text)) Explanation In the example shown, the formula in C11 is: =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,””)))/LEN(C2)) Note: The formula on this page counts instances of a word in a …

Continue Reading

How to count number of characters of text in a cell in Excel

by

If you need to count the total characters in a cell, you can do so using the LEN function. Formula =LEN(a1) Explanation In the example, the formula in the active cell is: =LEN(B3) The LEN function simply counts all characters that appear in a cell. All characters are counted, including space characters. Numbers are also …

Continue Reading