Excel VBA: Count Characters in Cell (5 Methods)

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.

Read More: How to Count Specific Characters in a Cell in Excel

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 the output is 5.

There are some more examples similar to the previous one.

Read More: How to Count Space Before Text in Excel

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 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.

Read More: How to Count Characters in Cell Including Spaces in Excel

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

Notes

• To view the code associated with 5 different methods, click the right button on the sheet name and select the View Code

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

Now, we know how to count characters in a cell or a range of cells using 5 different methods. Hopefully, it would help you to use these methods more confidently. Any questions or suggestions don’t forget to put them in the comment box below.

Related Articles

• How to Count Characters in Cell without Spaces in Excel
• How to Count Specific Characters in a Column in Excel
• How to Count Occurrences of Character in String in Excel

<< Go Back to Count Characters in Cell | String Manipulation | Learn Excel