Count number of characters of each word in a cell in excel

Question

For instance if I have the sentence in one cell:

The fox jumped twice

the output should be

3 3 6 5

I've tried using the len() and trim() functions in a couple of combinations but didn't manage to find a solution yet, was afraid that it could be done only by using VBA.

How many words can be in the different cells (the maximum?) It might be possible without VBA — Pierre44, Oct 17 '18 at 15:20
Old thread, but this is possible with normal function relatively easy. Have a look at [this](https://stackoverflow.com/a/58674097/9758194) — JvdV, Jun 16 '20 at 12:56

BruceWayne · Accepted Answer · 2018-10-17T23:20:45.567

2

Function count_Chars(ByVal target As Range)
Dim splt

splt = Split(target, " ")

Dim i As Long
Dim output As String

For i = LBound(splt) To UBound(splt)
    output = output & " " & Len(splt(i))
Next i

count_Chars = TRIM(output)
End Function

edited Oct 17 '18 at 23:20

answered Oct 17 '18 at 15:19

BruceWayne

22,923
15
65
110

Thank you so much! Saved me a lot of time – David Gorgan Oct 17 '18 at 15:31
@DavidGorgan - there are a few ways to do this, as the multiple answers show. Whichever one works for you as your Answer, would you mind marking it as such by clicking the check mark left of the post? – BruceWayne Oct 17 '18 at 15:37

Musicodelic · Answer 2 · 2018-10-17T16:42:41.157

maybe with a loop? Presuming that the words are separated by spaces:

Sub CountChars
    Dim strWord as Variant, lenWord as Long, StrCount as String

    For Each strWord in Split(mysheet.range("A1")," ")
        lenWord = len(strWord)

        'if you wish to display side by side:
        StrCount = StrCount & cstr(lenword) & " "
    Next
    StrCount = Trim(StrCount) 'To remove the space at the end from the last loop
    Debug.print StrCount

End Sub

score 1 · Answer 3 · answered Oct 17 '18 at 15:21

1

Try this short array based UDF.

Option Explicit

Function wordLength(str As String)
    Dim i As Long, arr As Variant
    arr = Split(str, Chr(32))
    For i = LBound(arr) To UBound(arr)
        arr(i) = Len(arr(i))
    Next i
    wordLength = Join(arr, Chr(32))
End Function

answered Oct 17 '18 at 15:21

Just curious - why use `Chr(32)` instead of `" "`? – BruceWayne Oct 17 '18 at 15:27
Thank you so much! – David Gorgan Oct 17 '18 at 15:31

score 1 · Answer 4 · answered Oct 17 '18 at 15:31

A User-Defined-Function is a good idea, indeed:

Public Function CountWords(inString As String) As String

    Dim i As Long
    Dim myArr As Variant: myArr = Split(inString)
    For i = LBound(myArr) To UBound(myArr)
        CountWords = CountWords & " " & Len(myArr(i))
    Next i
    CountWords = Trim(CountWords)

End Function

The Split() does not need an argument what to split with, if it is space;
The Trim() removes the last space;

score 1 · Answer 5 · answered Oct 17 '18 at 15:41

Just to show the horrible answer you get without VBA:

You can look for the " " and then count the length between the different spaces.

=find(" ",B3,1)-1&" "&
find(" ",B3,find(" ",B3,1)+1)-(find(" ",B3,1))-1&" "&
find(" ",B3,find(" ",B3,find(" ",B3,1)+1)+1)-(find(" ",B3,find(" ",B3,1)+1)-(find(" ",B3,1))+find(" ",B3,1))-1&" "&
LEN(B3)-(find(" ",B3,1)+find(" ",B3,find(" ",B3,1)+1)-(find(" ",B3,1))+find(" ",B3,find(" ",B3,find(" ",B3,1)+1)+1)-(find(" ",B3,find(" ",B3,1)+1)-(find(" ",B3,1))+find(" ",B3,1)))

Woah, I really appreciate your effort xD. – David Gorgan Oct 17 '18 at 15:58 — David Gorgan, Oct 17 '18 at 15:58

score 0 · Answer 6 · answered Oct 17 '18 at 15:17

You could use the Characters.Count property.

I got the following script from here.

Sub MakeSuperscript() 
 Dim n As Integer 

 n = Worksheets("Sheet1").Range("A1").Characters.Count 
 Worksheets("Sheet1").Range("A1").Characters(n, 1) _ 
 .Font.Superscript = True 
End Sub

So in your case I believe if you were to use the .split() function to create an array of words. Then while looping through each element of the array you can use the Characters.count() property.

Count number of characters of each word in a cell in excel

6 Answers6