Author: Oscar Cronquist Article last updated on October 09, 2018

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

User defined function:

=FreqWords(cell_range, position)+ Ctrl + Shift + Enter

Udf in cell E3:E30:

=FreqWords(B2:C11, 1)+ Ctrl + Shift + Enter

How to create an array formula

  1. Select cell range E3:E30.
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  3. Press and hold Ctrl + Shift.
  4. Press Enter once.
  5. Release all keys.

Udf in cell F3:F30

=FreqWords(B2:C11, 2) + Ctrl + Shift + Enter


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)
  ReDim Preserve nr(UBound(nr) - 1)
  FreqWords = Application.Transpose(nr)
End If
End Function

How to use user defined function in excel

  1. Press Alt-F11 to open visual basic editor
  2. Click Module on the Insert menu
  3. Copy and paste vba code
  4. Exit visual basic editor

Download excel example file

Frequent words.xls
(Excel 97-2003  Workbook *.xls)