# Excel VBA: Count Characters in Cell (5 Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates 5 different methods to count the number of characters in a cell or a range of cells using VBA code in Excel. These methods will certainly guide you to count the total number of characters in a range of cells, the number of occurrences of a specific character in a cell, and the character numbers of a cell at its simplest form. Let’s dive into the examples to get a clear understanding of the methods.

## Excel VBA to Count Characters in Cell: 5 Methods

To count the number of characters in a cell or a range of cells we would use several VBA functions and properties in our code. The following section describes how to open and write code in the visual basic editor.

Write Code in Visual Basic Editor

Follow the steps to open the visual basic editor and write some code there.

• Go to the Developer tab from the Excel Ribbon.
• Click the Visual Basic option. • In the Visual Basic For Applications window, click the Insert dropdown to select the New Module Now that a new module is opened, write some code there and press F5 to run.

### 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. In this example, we’ll count the number of characters in a cell using the VBA Len function. Let’s say, 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`````` Similarly, we can get the number of characters in a cell easily using the VBA Len function.

### 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 this example, 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`````` Similarly, we can get the number of characters in a cell easily using the Characters.Count property in our VBA code. ### 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.

In this example, we’re going to 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))``
• Finally, returns the difference of the character numbers of the series before and after substitution and output is 5. There are some more examples similar to the previous one. ### 4. Run a VBA Code to Count Sum of All Characters in a Range of Cells in Excel

In this example, 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 total 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.

In this example, we’re going to count the total characters of the range B5:B8 and print the output in cell C10. here we can 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 in 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. ### 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`````` Read More: How to Count Alphabet in Excel Sheet Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  