VBA to count number of words in cell

[Godders199]

Hello, I am trying to find the vba to count the number ofwords in a cell, and have it run down to the last row.

Manually I have worked out the formula which is which iscurrently in column AB
=LEN(TRIM(P2))-LEN(SUBSTITUTE(P2," ",""))+1
I have been searching the internet, and found a code likethis, but playing about I can not get it to work

Sheets("submissions").Select
Dim dic As Object, cell As Range, temp As Variant
Dim i As Long
Set dic = CreateObject("scripting.dictionary")
With dic
For Each cell InRange("p1" & Cells(Rows.count, "A").End(xlUp).Row)
.RemoveAll
IfLen(cell.Value) > 0 Then
temp =Split(cell.Value, " ")
For i = 0To UBound(temp)
If Not.Exists(temp(i)) Then .Add temp(i), temp(i)
Next i
cell.Value= Join(.Keys, " ")
End If
Next cell
End With

Just wondered if anyone has any examples of code they could letme see and amend to fit my requirements.

Thanks

 

[mumps]

Do you simply want to get a count of the words in each cell?

 

[kvsrinivasamurthy]

Try this code

Sub CountWords()


Dim TotWds As Long
Dim cel As Range


For Each cel In Range("p1:p" & Cells(Rows.Count, "A").End(xlUp).Row)
M = Split(cel, " ")
TotWds = TotWds + UBound(M) + 1
Next cel


End Sub

 

[Fluff]

If you want the total number of words in each cell try

Sub Godders199a()
   Dim Cl As Range
   
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      Cl.Offset(, 1) = UBound(Split(Cl)) + 1
   Next Cl
End Sub

If you want the total number of unique words per cell try

Sub Godders199b()
   Dim Cl As Range
   Dim Tmp As Variant
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         Tmp = Split(Cl)
         For i = 0 To UBound(Tmp)
            .Item(Tmp(i)) = .Item(Tmp(i)) + 1
         Next i
         Cl.Offset(, 1) = .Count
         .RemoveAll
      Next Cl
   End With
End Sub

 

[Godders199]

thanks all much appreciated

 

[Fluff]

You're welcome