## Word frequency [UDF]

*Article last updated on January 20, 2018*

This user defined function creates an unique list of words and their frequency in selected range.

### User defined function:

### Udf in cell E3:E30:

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

### Udf in cell F3:F30

### VBA

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 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 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 Function

**How to use user defined function in excel**

- 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 example file**

Frequent words.xls

(Excel 97-2003 Workbook *.xls)

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Count how many times a string exists in a cell range (case insensitive)

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Extract unique distinct values from a filtered table [udf and array 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 blog post describes how to list files in a folder and subfolders using vba. Where to copy vba code? […]

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

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

### 17 Responses to “Word frequency [UDF]”

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

Use the img tag, like this: <img src="Insert pic link here">

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