How to Count Word Frequency in Excel With TEXTSPLIT

To count word frequency in Excel, you turn the text into a list with one word per row, then count how often each word appears. In Microsoft 365, three functions do it: TEXTSPLIT splits the text into words, UNIQUE lists each word once, and COUNTIF counts each one. This guide is for anyone analysing survey comments, customer reviews, support tickets or job descriptions who wants to see which words come up most.

The example data#

All examples use four short customer comments in A2:A5:

The goal is a table of each word and its count, with "delivery" (4) and "late" (3) at the top.

TEXTSPLIT needs Microsoft 365 or Excel 2024. UNIQUE and SORTBY need Excel 2021 or later. For older versions, see the last section.

Step 1: split the text into one word per row with TEXTSPLIT#

TEXTJOIN joins all the comments into one long text, and TEXTSPLIT cuts it into words. In C2:

=TEXTSPLIT(TEXTJOIN(" ",TRUE,LOWER(A2:A5)),,{" ",",","."},TRUE)

Here is what each part does:

• LOWER(A2:A5) makes every letter lower case, so "Late" and "late" look the same.
• TEXTJOIN(" ",TRUE, ...) joins the four comments with a space between them. TRUE skips empty cells.
• In TEXTSPLIT, the column delimiter is left empty (,,), so the text is split into rows, not columns.
• {" ",",","."} is a list of delimiters: space, comma and full stop. The braces let you pass several at once.
• The last TRUE drops empty pieces, such as the gap between a comma and the space after it.

The formula spills 20 words down column C, one per row. The guide to the TEXTSPLIT function and the TEXTSPLIT function reference explain every argument.

Add any other punctuation your text uses, such as "!", "?", ";" or ":", to the list in braces.

Step 2: list each word once with UNIQUE#

UNIQUE returns each distinct value from a list once. In E2:

=UNIQUE(C2#)

C2# is a spill reference: it means "the whole list that spilled from C2", however long it is. The result is 13 distinct words, in the order they first appear: delivery, was, late, again, great, service and so on. The UNIQUE function guide and the article on counting unique values cover UNIQUE in more depth.

UNIQUE is not case-sensitive, so it would treat "Late" and "late" as one word even without LOWER. LOWER just makes the list look consistent.

Step 3: count each word with COUNTIF#

COUNTIF counts how many cells in a range match a value. Point it at the spilled word list, with the unique words as the criteria. In F2:

=COUNTIF(C2#,E2#)

Because E2# holds 13 words, the formula spills 13 counts, one for each word:

The other seven words appear once each. The counts add up to 20, the number of words in C2#, which is a quick check that nothing was lost. The COUNTIF function guide and the article on counting by category show the same pattern for other lists.

COUNTIF needs a range as its first argument. That is why the words are spilled into column C first. A formula like =COUNTIF(TEXTSPLIT(...),"late") returns an error, because TEXTSPLIT's result inside a formula is an array, not a range.

Step 4: sort words by frequency#

SORTBY sorts one list by the values in another. To put the most common words first, in H2:

=SORTBY(E2#,F2#,-1)

and in I2:

=SORT(F2#,,-1)

The -1 means largest first. The top of the list is now delivery (4), late (3), was (2) and great (2). Words with the same count keep their original order.

Remove common words like "the" and "and"#

Short common words such as "the", "and" and "was" are called stop words. They usually top a frequency list but say little. Type the words you want to ignore in K2:K10, then filter them out of the word list before counting. In D2:

=FILTER(C2#,COUNTIF($K$2:$K$10,C2#)=0)

COUNTIF checks each word against the stop list. A count of 0 means the word is not a stop word, so FILTER keeps it. Then use D2# instead of C2# in the UNIQUE and COUNTIF formulas.

Count one word without a frequency table#

To count a single word, you don't need the whole table. In Microsoft 365, compare the split words with the word you want and add the TRUE results:

=SUM(--(TEXTSPLIT(TEXTJOIN(" ",TRUE,LOWER(A2:A5)),,{" ",",","."},TRUE)="delivery"))

The result is 4. The comparison returns TRUE or FALSE for each word, and -- turns them into 1 and 0 so SUM can add them.

In older versions, a classic text trick counts how many times a word appears in a cell:

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),"late","")))/LEN("late")

SUBSTITUTE removes every "late", and the drop in length divided by the word's length is the count. The catch: it also counts "late" inside "latest" or "chocolate". The guide to counting characters and words explains the trick in detail.

Common word frequency problems#

Messy text gives messy counts. Check these:

1. Extra spaces or line breaks. Double spaces are handled by the TRUE at the end of TEXTSPLIT, but line breaks are not. Add CHAR(10) to the delimiter list, or clean the text with CLEAN and TRIM first.
2. Missing punctuation in the delimiter list. "late!" and "late" are counted as different words.
3. Too much text. TEXTJOIN's result can't be longer than 32,767 characters. For long text, split each cell separately, or work on a sample.
4. Old Excel. Without TEXTSPLIT, use Data > Text to Columns to split the words, stack them into one column, then count them with COUNTIF or a PivotTable.

Practise it#

Word frequency combines three everyday skills: cleaning text, listing unique values and counting matches. Practise cleaning messy text in a live spreadsheet, practise the UNIQUE function, and practise COUNTIF. ExcelReady checks each formula as you type. Start with the "delivery" count in the exercise on this page.