## Excel udf: Word frequency

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)

### 16 Responses to “Excel udf: Word frequency”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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!