count the number of members in cells seperated by ;

Hello,

i have a big wordlist and I want to count the number of all members in its coulm D. And also need the number of members in each cell in column D. The number of signs or letters in each member should be counted as well. The seperator is ";"

The macro is supposed to do:
--> count the number of members of each cell in Column D and put that in Column F
--> count the number letters or signs of each member and put the results in column E
--> give the total number of cell members in Cell F1

Thanks for each help in advance

See the example image:

Count the number of semicolons and add one?

Yes, but there are some empty cells as well. If a cell is empty then it would wrongly count it as one member, if only the ; are counted.

OK, so the logic would be something like:

IF the cell is empty then 0;
IF the cell has no semicolon then 1;
In all other cases, (number of semicolons + 1)

Or, more simply:

IF (the cell is empty, 0, (number of semicolons + 1))

Hi,
Give this a try!

Public Sub SplitIt()
Dim lR As Long
Dim i
Dim x
Dim j
lR = Cells(Rows.Count, "D").End(xlUp).Row

For i = 2 To lR
    x = Split(Cells(i, 4).Value, ";")
    For j = 0 To UBound(x)
        If j = 0 Then
            Cells(i, 5).Value = Len(x(j))
        Else
            Cells(i, 5).Value = Cells(i, 5).Value & ";" & Len(x(j))
        End If
    Next j
    Cells(i, 6) = UBound(x) + 1
Next i
Cells(1, 6).Formula = "=SUM(F2:F" & lR & ")"
End Sub

Tony

Thanks alot. Its working and its a great help. Thanks a million times