Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Get column name from index in Excel Table
  • Conditional Formatting Data bars Examples in Excel
  • How To Filter Data in Excel
  • Excel Pie Chart
  • How to Create One and Two Variable Data Tables in Excel

References

  • How to get last column number in range in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to get relative column numbers in a range in Excel
  • To count total rows in a range in Excel
  • How to reference named range different sheet in Excel

Data Validations

  • Excel Data validation unique values only
  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain
  • Excel Data validation exists in list

How to count specific words in a cell in Excel

by

If you need to count how many times a specific a word (or any substring) appears inside a cell, you can use a formula that uses SUBSTITUTE and LEN.

Formula

=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)

Explanation

In our example, we are using this formula:

=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)

How this formula works

B4 is the cell we’re counting words in, and C4 contains the substring (word or any substring) you are counting.

SUBSTITUTE removes the substring from the original text and LEN calculates the length of the text without the substring. This number is then subtracted from the length of the original text. The result is the number of characters that were removed by SUBSTITUTE.

Worked Example:   Remove last characters from right in a cell in Excel

Finally, the number of characters removed is divided by the length of the substring. So, if a substring is 5 characters long, and there are 10 characters missing after it’s been removed from the original text, we know the substring appeared twice in the original text.

Handling problems

Counting words in Excel is tricky because Excel doesn’t support regular expressions. As a result, it’s difficult to build matches that target the words you want to count exactly, while ignoring substrings and other partial matches (look for “fox ” to avoid “foxes”). Punctuation and case variations make this problem quite challenging.

Worked Example:   Highlight cells that end with in Excel

One workaround is to use another formula in a helper column to “normalize text” as a first step. Then use the formula on this page to count words wrapped in space characters to get an accurate count (i.e. you can look for ” fox ” in the normalized text.

Note: this approach is only as good as the normalized text you are able to create, and you might need to adjust the normalizing formula many times to get the result you need.

Worked Example:   Extract last name from full name -- Manipulating NAMES in Excel

Handling case

SUBSTITUTE is a case-sensitive function, so it will match case when running a substitution. If you need to count both upper and lower case occurrences of a word or substring, use the UPPER function inside SUBSTITUTE to convert the text to uppercase before running the substitution:

=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)

Because this formula converts the substring and the text to uppercase before performing the substitution, it will work equally well with text in any case.

Post navigation

Previous Post:

How to get last line in cell in Excel

Next Post:

How to find nth occurrence of character in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to return blank in place of #DIV/0! error in Excel
  • Excel If, Nested If, And/Or Criteria Examples
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function

Date Time

  • How to determine year is a leap year in Excel
  • How to get year from date in Excel
  • Count dates in current month in Excel
  • Display Date is workday in Excel
  • Convert time to time zone in Excel

Grouping

  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel
  • How to randomly assign people to groups in Excel

General

  • How to make excel worksheets print on one page?
  • Index and match on multiple columns in Excel
  • How to count total columns in range in Excel
  • How to Insert Cells, Row and Rows in Excel
  • Excel Operators
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning