Excel VBA: Count Characters in Cell: 5 Methods

Method 1 – Use of VBA Len Function to Count Characters in a Cell in Excel

The LEN function in VBA Excel returns the number of characters in a text or string. The syntax of this function is-

Len(Expression)

The Expression argument can be supplied as a string directly in the function or as a variable, or as a range object. Count the number of characters in a cell using the VBA Len function. We have a bunch of strings and numbers in cells B5:B9.

Copy and paste the following VBA code to get the character numbers in cell B5. The code will print the value in cell C5.

Sub CountCharactersInCell()
Range("C5") = Len(Range("B5"))
End Sub

Get the number of characters in a cell easily using the VBA Len function.

Method 2 – Apply the Characters.Count Property of VBA Excel to Count Characters in a Cell

We can do the same as the previous method by using the Characters.Count property of Excel VBA. The generic code to count the number of characters using the property is like.

Range(“A1”).Characters.Count

In cell C5, we put the number of characters in cell B5 using the following code.

Sub CountCharactersInCell()
Range("C5") = Range("B5").Characters.Count
End Sub

Get the number of characters in a cell easily using the Characters.Count property in our VBA code.

Method 3 – Count Occurrences of a Character in a Cell Using VBA in Excel

Using the VBA Replace function with the Len function, we can count the number of occurrences of a character(s) in a cell. The Replace function returns a string after substituting a substring of the string with another substring.

Find the number of commas in a series of numbers in cell B5. Run the following code to get the value of occurrences.

Sub CountOccurancesOfCharactersInCell()
Range("C5") = Len(Range("B5")) - Len(Replace(Range("B5"), ",", "", 1, -1, vbTextCompare))
End Sub

The output in cell C5 is 5, which means the series 1,2,3,4,5,6 have 5 commas in it.

Code Explanation:

Len(Range("B5"))

Returns the number of characters of the series 1,2,3,4,5,6

Replace(Range("B5"), ",", "", 1, -1, vbTextCompare)

Substitutes the commas inside the series 1,2,3,4,5,6 with blank and returns 123456.

Len(Replace(Range("B5"), ",", "", 1, -1, vbTextCompare))

Returns the numbers of character of the substituted string 123456 that is 6.

Len(Range("B5")) - Len(Replace(Range("B5"), ",", "", 1, -1, vbTextCompare))

Returns the difference of the character numbers of the series before and after substitution and the output is 5.

More examples similar to the previous one.

Method 4 – Run a VBA Code to Count Sum of All Characters in a Range of Cells in Excel

We’ll show how to use the SUMPRODUCT function along with the VBA Len function with the help of Range.Formula property of VBA Excel to count the number of characters in a range of cells. The Len function counts the number of characters in each of the cells of the selected range and the SUMPRODUCT function sums the numbers to output the total.

Count the total characters of the range B5:B8 and print the output in cell C10. See the character numbers of each of the cells in B5:B8 which we calculated in the previous section.

Copy and paste the following code into the Visual Basic Studio and press F5 to accomplish the task.

Sub CountTotalCharactersInRange()
ActiveSheet.Range("C10").Formula = "=SUMPRODUCT(LEN(B5:B8))"
End Sub

The output is 48, the sum of the character numbers of cells B5:B8 i.e.,12+13+11+12=48.

Method 5 – Use a For Loop to Count Total Number of Characters in a Range of Cell Using VBA in Excel

To get the total number of characters in a range of cells, we can also use a For Next Loop to run a counter that sums the character numbers of each cell in the range. The following code is configured for calculating the total number of characters in the cell range B5:B8 and to get the output in cell C10.

Sub CountTotalCharactersInRange()
totalchar = 0
For x = 5 To 8
totalchar = totalchar + Len(Range("B" & x).Value)
Next x
ActiveSheet.Range("C10") = totalchar
End Sub