# Cell character counting formula?

I need a formula that counts the total number of characters in a cell but also will count how many times that number of characters occurs.

For example in column A:

A range of 10 cells with 201, 202, 203, 204, 205, 1001, 1002, 1003, 1004, 1005.

There are 5 cells with 3 characters and 5 cells with 4 characters.

What would the formula be for this? Not sure how high I would need to go in terms of how many characters in each cell to count but I'm hoping that's an easy edit in the formula later on.

## 4 Answers Cell character counting formula?

For the number of chars, use another column with `=len(cell)`.

Then make a dynamic table where you put the len column at the left side of the table and the len column again at the middle. Make sure the table is saying "count of len" or something very similar to this.

### Daniel Möller4 months ago

With data in column A, in B1 enter:

``````=SUMPRODUCT(--(LEN(A:A)=ROW()))
``````

and copy downwards: ### Gary's Student4 months ago

Add a helper column with the formula `=len(A1)` and copy that for column A. Then you can have a list of the different values 3,4,5... and `countif(B:B,C1)` and that will give you the count of values with length 3. # Cell Characters Count Array Version

``````Option Explicit

Sub CellCharactersCount()

Const cVntWsName As Variant = "Sheet1"    ' Worksheet Name or Index
Const cStrFirstSource As String = "A1"    ' First Cell of Source Column
Const cStrFirstTarget As String = "B1"    ' First Cell of Target Column

Dim vntSource As Variant                  ' Source Array
Dim vntLngTarget As Variant               ' Target Array (As Long)
Dim lng1 As Long, lng2 As Long            ' Array Row Counters
Dim lngMax As Long                        ' Maximum Characters

' Paste range into Source Array
With ThisWorkbook.Worksheets(cVntWsName)
vntSource = .Range(.Range(cStrFirstSource), .Cells(Rows.Count, _
.Range(cStrFirstSource).Column).End(xlUp))
End With

' Count the maximum number of chars.
For lng1 = 1 To UBound(vntSource)
If Len(vntSource(lng1, 1)) > lngMax Then
lngMax = Len(vntSource(lng1, 1))
End If
Next

' Copy Len Data to Target Array.
ReDim vntLngTarget(1 To lngMax, 1 To 1) As Long
For lng1 = 1 To UBound(vntSource)
If Len(vntSource(lng1, 1)) <> 0 Then
For lng2 = 1 To lngMax
If Len(vntSource(lng1, 1)) = lng2 Then
vntLngTarget(lng2, 1) = vntLngTarget(lng2, 1) + 1
End If
Next
End If
Next

With ThisWorkbook.Worksheets(cVntWsName)
' Clear contents of Target Column Range.
.Range(cStrFirstTarget) _
.Resize(Rows.Count - .Range(cStrFirstTarget).Row + 1).ClearContents
' Paste Target Array into Target Range.
.Range(cStrFirstTarget).Resize(lngMax) = vntLngTarget
End With

End Sub
``````