Count number of letters in words

[wab]

Hi,
In need Excel to count the number of characters in individual words, excluding the spaces between them. ie:

Cell A1 contains:
<TABLE style="WIDTH: 271pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=361><COLGROUP><COL style="WIDTH: 271pt; mso-width-source: userset; mso-width-alt: 13202" width=361><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 271pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=361>Brave New World
Result needed = 5,3,5.

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" id=td_post_264496 height=20>
Cell A2 contains:
The Devil's Discus
Result needed = 3,7,6.

Does anyone know if this is possible or am I living in a dream world!? I have 850 cells to count and don't want to have to do them manually so any help would be greatly appreciated

Thanks
Wayne





</TD></TR></TBODY></TABLE>

 

[VoG]

With a User Defined Function in a regular module

Function CountLetters(r As Range) As String
Dim X
Dim i As Long
X = Split(r.Value)
For i = LBound(X) To UBound(X)
    CountLetters = CountLetters & Len(X(i)) & ", "
Next i
CountLetters = Left(CountLetters, Len(CountLetters) - 2)
End Function

Excel Workbook
	A	B
1	Brave new world	5, 3, 5
Sheet2

 

[wab]

That is just what I needed! You have just saved me hours of tedious work. I can't thank you enough!

 

[pgc01]

Another similar udf:

Function CountLetters(s As String) As String
Dim v As Variant
Dim i As Long
 
v = Split(s)
For i = LBound(v) To UBound(v)
    v(i) = Len(v(i))
Next i
CountLetters = Join(v, ", ")
End Function