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
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öller 4 months ago
Gary's Student 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.
JoeJam 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, _ .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