Excel VBA - Long format word analysis two challenges

0
votes

I want to be able to the following two actions; I know the Excel Formulas but I'm hoping there is a way to program this w/ VBA.

  1. I'm using the excel formula to perform a word count for each cell with the output in Column B:

=LEN(TRIM($A2))-LEN(SUBSTITUTE($A2," ",""))+1

Cells in Column A contain free-form text, ranging from 500-2,500 words and the deliminator is spaces. The number of cells with text varies from workbook to workbook.

So far I've tried entering the following code with no luck. I receive a type mismatch

Code:

Dim lastRow As Long

With Sheets("Sheet1")
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    With .Range("B2:B" & lastRow).Formula = "=IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A2,"" "",""""))+1)"
    .Value = .Value
    End With
End With
  1. The second step I'm trying to do is searching the column header (Columns C through BZ) and counting how many times that word appears in each cell.

=(LEN($A2)-LEN(SUBSTITUTE(UPPER($A2),UPPER(C$2),"")))/LEN(C$1)

Unlike the problem above I've not been able to start a script.

3
vbaexcel
Start by doing a search for counting words in a string [excel]. You'll find plenty of examples on stackoverflow. There will also be examples for counting cells in a string if they appear in a range. That should get you started. Then, if you have problems, post back by editing your question. Please read the HELP pages for information as to What topics can I ask about here?, How to Ask a Good Question, and How to create a Minimal, Complete, and Verifiable example – Ron Rosenfeld
What have you tried so far? And what is the deliminator for the words in the cell? – VBA Pete
@Ron - thanks for the advice still getting the hang of the site – Justin
@Justin As you can see, your code in your comment is practically useless for helping you in part due to the formatting, and in part due to the absence of any question. Please note in my comment I wrote: if you have problems, post back by editing your question. Also read the topics I suggested if you are having a problem. – Ron Rosenfeld
@RonRosenfeld I quickly saw the issue with responding in comments and have rewritten the question in a way that hopefully better explains my challenges. – Justin

3 Answers

3
votes

A VBA macro would probably execute fastest for this purpose. Here is one approach. I have assumed that space is the word delimiter, and I am using Regular Expressions to get the count of each column header in the sentence in column 1. Punctuated words may not return what you expect, so check them out in your examples. Note below that Dog will match Dog's. If that is not what you want, easy to change.

Read the comments within the code You will need to change "sheet1" to the correct sheetname.

Option Explicit
'Set reference to Microsoft VBScript Regular Expressions 5.5
Sub WordCounting()
    Dim WS As Worksheet, vSrc As Variant, R As Range
    Dim RE As RegExp, MC As MatchCollection
    Dim sPattern As String
    Dim V As Variant
    Dim I As Long, J As Long

Set WS = ThisWorkbook.Worksheets("sheet1")

With WS 'read into vba array for speed
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) _
        .Resize(columnsize:=.Cells(1, .Columns.Count).End(xlToLeft).Column)
    vSrc = R
End With

'get the word counts
Set RE = New RegExp
With RE
    .IgnoreCase = True
    .Global = True
For I = 2 To UBound(vSrc, 1)
    V = Split(vSrc(I, 1))
    vSrc(I, 2) = UBound(V) + 1 'Count of ALL Words

    For J = 3 To UBound(vSrc, 2)
        .Pattern = "\b" & vSrc(1, J) & "\b"
        Set MC = .Execute(vSrc(I, 1))
        vSrc(I, J) = MC.Count 'Count of Individual Word in column header
    Next J
Next I
End With

R = vSrc

End Sub

Here is an example of the output. This algorithm should scale OK to your size worksheet.

enter image description here

2
votes

I would create two VBA functions to find your solutions:

  1. Function to find total word count using deliminator space:

enter image description here

Code:

Public Function WordCount(allItems As String) As Long

Dim itemArray() As String
Dim totalsum As Variant

totalsum = 0
itemArray() = Split(allItems, " ")

totalSum = UBound(itemArray) + 1

WordCount = totalsum
End Function
  1. Function to count string in cell that match header (header has to be in row 1):

enter image description here

Public Function HeaderWordCount(allItems As String) As Long

Dim itemArray() As String

totalsum = 0
itemArray() = Split(allItems, " ")

For i = LBound(itemArray) To UBound(itemArray)
    If itemArray(i) = Cells(1, ActiveCell.Column).Value Then
    totalsum = totalsum + 1
    End If
Next i

HeaderWordCount = totalsum
End Function
0
votes

For what you are trying to do I would use a combination of LEN and SUBSTITUTE/REPLACE (depending on if you are doing this in Excel/VBA respectively). To count the number of words in a cell, you could count the number of spaces in the cell and then add 1- the formula would be as follows:

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

To count the number of times a string is contained in a cell it is a similar idea; formula would be as follows:

=LEN(A1)-LEN(SUBSTITUTE(A1,"EnterString",""))

N.B.- Make sure to replace "EnterString" with the correct cell reference containing the header string you want to search for...

Hope this helps, TheSilkCode