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.


Download Practice Workbook

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


5 Methods to Count Characters in a Cell Using VBA in Excel

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.

Excel VBA Count Characters in Cell

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

Excel VBA Count Characters in Cell

Similarly, we can get the number of characters in a cell easily using the VBA Len function.

Excel VBA Count Characters in Cell

Read More: Excel Count Specific Characters in Cell (4 Quick Ways)


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

Excel VBA Count Characters in Cell

Similarly, we can get the number of characters in a cell easily using the Characters.Count property in our VBA code.

Excel VBA Count Characters in Cell


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.

Excel VBA Count Characters in Cell

There are some more examples similar to the previous one.

Excel VBA Count Characters in Cell


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.

Excel VBA Count Characters in Cell

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.

Excel VBA Count Characters in Cell

Read More: How to Count Characters in Cell Including Spaces in Excel (5 Methods)


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

Excel VBA Count Characters in Cell

Read More: Count Numbers in a Cell in Excel (3 Methods)


Notes

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


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

Al Arafat Siddique

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

Leave a reply

ExcelDemy
Logo