## How to count word frequency in a 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**

### 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
- Click Module on the Insert menu
- Copy and paste vba code
- Exit visual basic editor

### Download Excel file

Extract unique distinct values from a filtered Excel defined Table [UDF and Formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

List files in a folder and subfolders [UDF]

This article demonstrates a user definedÂ function that lists files in a ggiven folder and subfolders. A user defined function is […]

Split words in a cell range into a cell each [UDF]

This post describes how to split words in a cell range into a cell each using a custom function. I […]

Split values equally into groups

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

Filter unique distinct words from a cell range [UDF]

This blog post describes how to create a list of unique distinct words from a cell range. Unique distinct words […]

Count comma separated values [UDF]

I received an email from one of my sevenÂ blog readers. In Excel, I have a column, say A, with some […]

Lookup multiple values in one cell [UDF]

Chrisa asks: Hi Oscar...this is a very interesting function and helped me a lot so far. My file though is […]

The "Text to columns" feature in excel lets you split multiple values in a single cell using a delimiting character, […]

Two-way lookup using multiple tables [UDF]

This article describes a User Defined Function that lookups values in multiple cross reference tables based on two conditions. A […]

### 17 Responses to “How to count word frequency in a cell range [UDF]”

### Leave a Reply to Sam

### 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.

**Contact Oscar**

You can contact me through this contact form

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 formulaSelect 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 […]