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.

4 months ago

With data in column A, in B1 enter:


and copy downwards:

enter image description here

4 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.enter image description here

4 months ago

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, _
  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

  ' 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
    End If

  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

4 months ago