Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. Excel 365 has many new functions that are really useful, I am using the REDUCE function combined with the LAMBDA and VSTACK functions here.
You can also use the TEXTJOIN and the TEXTSPLIT functions, however, they have a limit of 32 767 characters which the REDUCE function doesn't have.
This article also shows a user defined function (UDF) that will work for most Excel versions.
Table of Contents
1. How to calculate word frequency in a given cell range - Excel 365 LAMBDA function
Excel 365 formula in cell E3:
Formula in cell F3:
The formula in cell F3 is explained here: Count how many times a string exists in a cell range (case insensitive)
Explaining the formula in cell E3
Step 1 - Split string based on a space character
The TEXTSPLIT function splits a string into an array based on delimiting values.
Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])
TEXTSPLIT(x, " ")
Step 2 - Stack arrays vertically
The VSTACK function combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Function syntax: VSTACK(array1,[array2],...)
VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))
Step 3 - Build the LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))
Step 4 - Iterate through each cell in cell range B3:C12
The REDUCE function shrinks an array to an accumulated value, a LAMBDA function is needed to properly accumulate each value in order to return a total.
Function syntax: REDUCE([initial_value], array, lambda(accumulator, value))
REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " "))))
Step 5 - Replace errors with -
The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.
Function syntax: IFERROR(value, value_if_error)
IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-")
Step 6 - Rearrange array to a single column layout
The TOCOL function rearranges values in 2D cell ranges to a single column.
Function syntax: TOCOL(array, [ignore], [scan_by_col])
TOCOL(IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-"))
Step 7 - Extract unique distinct values from the array
The UNIQUE function returns a unique or unique distinct list.
Function syntax: UNIQUE(array,[by_col],[exactly_once])
UNIQUE(TOCOL(IFERROR(REDUCE(,B3:C12,LAMBDA(x,y,VSTACK(TEXTSPLIT(x, " "),TEXTSPLIT(y, " ")))),"-")))
2. How to calculate word frequency in a given cell range - UDF
This user defined function creates a unique distinct list of words and how many times they occur in the selected range.
User defined Function Syntax
FREQWORDS(cell_range, position)
Arguments
| cell_range | Required. The range you want to use. |
| position | Required. Which column to return. The first column contains the values and the second column contains their corresponding frequency. |
Example
Array formula in cell E3:E30:
Array formula in cell F3:F30:
How to create an array formula
-
- Select cell range E3:E30.
- Copy (Ctrl + c) array formula and paste (Ctrl + v)Â to formula bar.
- Press and hold Ctrl + Shift simultaneously.
- Press Enter once.
- Release all keys.
VBA
'Name function and declare arguments
Function FreqWords(tbl_array As Range, pos As Integer) As Variant()
'Declare variables and their data types
Dim cell As Variant, wrds As Variant, i As Integer
Dim a As Integer, j As Integer
Dim tmp() As String, nr() As Integer
'Redimension variable tmp so it can grow using Redim Preserve
ReDim tmp(0), nr(0)
'Assign 1 to first value in array variable nr
nr(0) = 1
'Iterate through cells in cell range
For Each cell In tbl_array
'Split words in cell
wrds = Split(cell)
'Iterate thorugh words
For i = 0 To UBound(wrds)
'Iterate through arrayvariable tmp
For j = 0 To UBound(tmp)
'If variable wrds equal variable tmp then increase value in variable nr by 1
If wrds(i) = tmp(j) Then
nr(j) = nr(j) + 1
a = 1
Exit For
End If
Next j
'Check if a is not equal to 1
If a <> 1 Then
'Copy value from variable wrds to tmp
tmp(UBound(tmp)) = wrds(i)
'Add another container to array variable tmp
ReDim Preserve tmp(UBound(tmp) + 1)
ReDim Preserve nr(UBound(tmp))
nr(UBound(tmp)) = 1
End If
a = 0
Next i
Next cell
'Return values in column 1 if argument pos is equal to 1
If pos = 1 Then
ReDim Preserve tmp(UBound(tmp) - 1)
FreqWords = Application.Transpose(tmp)
Else
'Return values in column 2 if argument pos is not equal to 1
ReDim Preserve nr(UBound(nr) - 1)
FreqWords = Application.Transpose(nr)
End If
End Function
How to add the User defined Function to your workbook
- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste vba code
- Exit visual basic editor
User defined function category
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a user defined function that lists files in a ggiven folder and subfolders. A user defined function is […]
The image above demonstrates a user-defined function in cell range B6:D7 that allows you to search a folder and subfolders […]
Excel categories
26 Responses to “Analyze word frequency in a cell range”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
While executing this function,I am facing #value error in the entire column. Please help.
Sam,
This udf is created in excel 2007, what excel version do you have?
Did you remember to enter the second argument?
=FreqWords(B2:C11, 1)
Did you create an array formula?
How to create an array formula
Select cell range E3:E30.
Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
Press and hold Ctrl + Shift.
Press Enter once.
Release all keys.
Hello Oscar,
first of all MERRY CHRISTMAS AND HAPPY NEW YEAR 2012- also Thank you very much for Word Frequency function.
I would like to ask if it is possible to convert it to a VBA Macro subroutine . I mean is it possible to use Freqwords function with in a sub(). I am novice to programming . please help.
Thank you very much sir.
Srinivas
Hi Oscar
Happy New Year 2012. Thank you very much for Word Frequency UDF. Forget about above request for sub(). I have found alternative.
Thank you
Srinivas
[...] Does this help?.... Excel udf: Word frequency | Get Digital Help - Microsoft Excel resource Example results from the above solution...... [...]
Hi Oscar,
I need your help to find the macro
I have many excel files with multiple sheets and each excel sheet
has many formula which are starting from perticular word e.g. FDS, FDSB, etc some formula has FDS, FDSB occur in the middle of the formula.
i need to find out how many times FDS,FDSB has been appear in the sheet(total count)
below is the formulae for your referance
=FISERROR(FDSB($D21, "IC_ESTIMATE_DATE(ALL, EXP_RPT, QTR, 3, 0, , , 'MM/DD/YYYY')@RC_ESTIMATE_DATE(ALL, EXP_RPT, QTR, +3, 0 , , , 'MM/DD/YYYY')"),"na)
in such a way there are many formulas in all the cells
i just need to count the how many times FDSB occur in one perticular sheet
please help
Best Regards
Rahul Jadhav
Pune
India
Rahul Jadhav,
Have you read this post:
Count multiple text strings in a cell range
Thank you
Rahul Jadhav,
Read this post:
Count text strings in formulas (vba)
Hi Oscar
Thanks for the reply this is what i was looking for thanks once again now i will apply it in all the worksheets.
One more thing i would like to ask is that
How can we identify any protected excel file before opening using macro VBA (e.g. i have a folder with multiple excel files i need to find out using macro how many files are password protected before opening and it should give result on separate workbook.)
is it possible using excel vba macro
Please reply
Best regards
Rahul Jadhav
rahul jadhav,
read this post:
Find out if excel files in a folder are password protected
[…] Rahul Jadhav asks: […]
[…] rahul jadhav asks: […]
Hi Oscar,
Thank you very much for the macro just to infrom you that it is working correctly
Thanks again
Best regards
Rahul Jadhav
Hi Oscar,
Hope you are doing good!!!
My requirement is to copy data from ms excel and paste into ms word on specific position
>Both the files should get open automatically specially ms word
>macro should identify the user define cursor position in ms word file
>then paste the data where user wants to
>In this way i want to copy tables data, text, from excel into ms word on user define posion.
please check and let me know the suitable solution for the same
Have a nice time ahead
Thanking you in advance
Best Regards
Rahul Jdhav
Awesome function!!! Thanks so much for sharing this!
[…] Excel udf: Word frequency […]
hello oscar,
why u'r formula cannot use in new file?
thanks
boboy,
You need to copy and paste the VBA code to a module in your new file (workbook).
Thank you for that.
The only issue i'm facing is that only the first word of each cell is displaying after entering the formula. Not the full list of each single keyword.
For example, in your spreadsheet, the word "Air" appears 6 times in my list, and the frequency is correct (9).
What am I doing wrong?
Valentino,
you need to enter the formula as an array formula. There are instructions in this article on how to create an array formula.
How do you get the word frequency results in the array to sort by largest to smallest?
Hi great formula!
Quick question - my apologies in advance if it is dumb I typically do not work in Excel.
When I define the array for the results return I am assuming I know the number of unique words I have (in your example you knew there are 27 unique values?). But what happens if I don't know the number of unique words? I selected as an array the whole column but I get many #N/A and then it very inconvenient because I cannot delete these (it tells me I cannot change the array).
Is there a way for it automatically to decide the size of the array?
*I realize supposedly it is a small issue but I am working with a very large file more than 5000 companies that each have many tags and trying to sort the frequency of the tags and it gets messy.
Thanks in advance!
Julie
Is there a way for it automatically to decide the size of the array?
Yes, Excel 365 subscribers don't need to enter this as an array formula. Excel takes care of the output and returns spilled values.
This UDF is for previous Excel versions. It will return blank values if the entered cell range is larger than the number of returned values.
Function FreqWords(tbl_array As Range, pos As Integer) As Variant() Dim cell As Variant, wrds As Variant, i As Integer Dim a As Integer, j As Integer Dim Row As Variant Dim tmp() As String, nr() As Integer ReDim tmp(0), nr(0) nr(0) = 1 For Each cell In tbl_array wrds = Split(cell) For i = 0 To UBound(wrds) For j = 0 To UBound(tmp) If wrds(i) = tmp(j) Then nr(j) = nr(j) + 1 a = 1 Exit For End If Next j If a <> 1 Then tmp(UBound(tmp)) = wrds(i) ReDim Preserve tmp(UBound(tmp) + 1) ReDim Preserve nr(UBound(tmp)) nr(UBound(tmp)) = 1 End If a = 0 Next i Next cell For Row = UBound(tmp) To Application.Caller.Rows.Count tmp(UBound(tmp)) = "" ReDim Preserve tmp(UBound(tmp) + 1) Next Row ReDim Preserve tmp(UBound(tmp) - 1) If pos = 1 Then ReDim Preserve tmp(UBound(tmp) - 1) FreqWords = Application.Transpose(tmp) Else ReDim Preserve nr(UBound(nr) - 1) FreqWords = Application.Transpose(nr) End If End FunctionHi Oscar.
Much like Julie, I mod'ed the formula for a single column, removed the array because I am using Excel 365 and am getting the #NAME? error. I double checked the formula call to the function name and it reads the same. Not sue what else to do...
Michael
Hello again.
Nevermind... just found a thread that suggested adding in the module name infant of the function call, so I renamed the call to Module1.FreqWords and it worked.
Michael