I received an email from one of my seven blog readers.
In Excel, I have a column, say A, with some cells blank and some cells with text.
In another column, say B, each *cell* contains many words, separated by a comma.
For every cell in B, I need to check to see how many of the words from column A are in the cell and output the total count of matches.
I built a user defined function for this, if you have a regular formula you think can solve this, please share. This animated picture explains it all.
The formula in cell C2 is a user defined function. You build a udf just like a macro using the visual basic of application language (vba).
The first argument (B2) in this custom made function is a cell reference to comma-separated values, in a single cell. The second argument ($A$2:$A$20) is a reference to cell range that you want to count, make sure it is a single column cell reference.
Build a user defined function
To build a user defined function, follow these steps:
- Press Alt + F11 to open the visual basic editor
- Click "Insert" on the menu
- Click "Module"
- Copy the code below and paste it to the code module.
User defined function vba code
Function CountWords(a As String, b As Range) Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single Words = Split(a, ",") For Each Value In Words For Each cell In b If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1 Next cell Next Value CountWords = c End Function
Explaining the user defined function
Function name and arguments
A user defined function procedure always start with "Function" and then a name. This udf has two arguments, a and b. Variable a is a string and b is a range.
Function CountWords(a As String, b As Range)
Dim Words() As String Dim Value As Variant, cell As Variant Dim c As Single
Words() is a dynamic string array. Value and cell are variants. c is a single data type. Read more about Defining data types.
Words = Split(a, ",")
The Split function accepts a text string and returns a zero-based, one-dimensional array containing all sub strings. Split allows you also to specify a delimiting character, default is the space character.
For example, a is "aa,ee,gg". The delimiting character is a comma ",". Words is then an array containing these values: "aa", "ee", "gg".
For ... Next statement
For Each Value In Words ... Next Value
Repeats a group of statements a specified number of times. In this case as many times as there are values in the array Words.
If UCase(WorksheetFunction.Trim(cell)) = UCase(WorksheetFunction.Trim(Value)) Then c = c + 1
The Ucase function converts a string to uppercase letters. The WorksheetFunction.Trim method removes all spaces from text except for single spaces between words.
The If function compares cell and Value and if they match 1 is added to c.
The udf returns...
CountWords = c
The udf returns the value in c.
End a udf
A function procedure ends with the "End function" statement.
- Excel udf: Lookup and return multiple values concatenated into one cell
- Filter unique words from a range in excel (udf)
- Excel udf: Filter unique distinct values (case sensitive)
- Excel udf: Filter unique distinct records (case sensitive)
- User defined function to split words in a cell range into a cell each in excel
- Excel udf: Filter common values between two cell ranges in excel
- Excel udf: Filter values existing only in one out of two ranges
Download excel * .xlsm file