ExcelInsider Team
Counting the number of characters in a string is a common task for data validation, analysis, and cleaning in Excel. While the built-in LEN function is useful, using VBA provides a more dynamic and automated approach. VBA macro will help you to count characters across multiple cells based on specific criteria. In this article, we will guide you through various VBA methods to count characters, with and without spaces, and even count specific characters or just words.
To count the number of characters in a string using VBA, here is one simple solution.
➤ Open the Visual Basic Editor by pressing Alt + F11 or from the Developer tab.
➤ Insert a new module and paste the provided code.
Sub Count_Characters()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Sheets("Number of Characters")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 3).Value = Len(ws.Cells(i, 2).Value)
Next i
End Sub➤ Run the macro to automatically calculate and display the character count in your worksheet.
Counting Number of All Characters in String
In this method, we will use the LEN function in the VBA macro to count every character, including spaces, numbers, and special symbols, in a selected range of cells. This method is perfect for getting the total length of strings.
Let’s use the sample data below to count the total characters in the strings in column B.
➤ Go to the Developer tab and click on Visual Basic.
➤ In the VBA editor, go to Insert > Module.
➤ Now, copy and paste the following code into the new module window.
➤ Click the Run button to execute the code.
Sub Count_Characters()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Sheets("Number of Characters")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 3).Value = Len(ws.Cells(i, 2).Value)
Next i
End Sub➧ For i = 2 To lastRow: This loop goes through each row, starting from row 2 (to skip the header) and continuing until it reaches the last row with data in the column.
➧ ws.Cells(i, 3).Value = Len(ws.Cells(i, 2).Value): This is the core of the code. It uses the LEN function to count the total number of characters in the cell in column B of the current row and then places that count into the cell in column C.
After running the macro, the character count for each string will appear in column C.
Applying a User-Defined Function to Count Characters
For more flexibility, you can create a user-defined function that works just like a regular Excel formula. This allows you to call the function directly from any cell and count the characters in a specified string.
➤ In the VBA editor, click Insert > Module, and paste the following code.
➤ Click the Save button.
Function CountChars(txt As String) As Long
CountChars = Len(txt)
End Function➧ CountChars = Len(txt): This is the main part of the function. It tells the function to return the character count of the input text using the built-in LEN function.
➤ Return to the worksheet, choose a cell and type the formula below.
=CountChars(B2)
➤ Press ENTER and drag the Fill Handle down.
Finally, the function will immediately return the total number of characters.
Calculating Characters Without Spaces
Sometimes, you might need to count the character of a string excluding spaces. A simple macro can achieve this by using the REPLACE function to remove all spaces before counting the remaining characters with the LEN function.
➤ In the VBA editor, insert a new module and paste the following code.
➤ Hit the Run button.
Sub CountCharacters_withoutSpaces()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Set ws = ThisWorkbook.Sheets("Without Spaces")
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 3).Value = Len(Replace(ws.Cells(i, 2).Value, " ", ""))
Next i
End Sub➧ ws.Cells(i, 3).Value = Len(Replace(ws.Cells(i, 2).Value, " ", "")): This is the key line. The REPLACE function first removes all spaces from the cell value. Then, the LEN function counts the characters of this new, space-free string. The final count is then entered into the cell in column C.
As a result, you will get the character count without spaces in the result column.
Finding Specific Characters in a String
This method is useful when you want to count the occurrences of a particular character, such as an @ symbol or a specific letter, within a string. Here, we will count specific character from a single string placed in cell B3.
➤ Insert a new module in the VBA editor and paste the code below.
➤ Press F5 or click the Run button to run the macro.
Sub CountSpecificCharacter()
Dim txt As String, charCount As Long
txt = Range("B3").Value
charCount = Len(txt) - Len(Replace(txt, "@", ""))
MsgBox "Number of '@' characters in Email: " & charCount
End Sub➧ charCount = Len(txt) - Len(Replace(txt, "@", "")): This line is an appropriate way to count a specific character. It subtracts the length of the string after all "@" symbols have been removed from the original string length. The difference gives you the exact number of times the "@" symbol appeared.
Thus, a message box will pop up displaying the count of the specified character.
Counting Only Words in a String
To count only the number of words in a string, you can create a user-defined function. Here, this function will run through the string, counting only alphabetical characters, ignoring numbers, spaces, and symbols.
➤ In the VBA editor, insert a new module and paste the following code.
➤ Click the Save button.
Function CountLettersOnly(txt As String) As Long
Dim i As Long, c As String, cnt As Long
For i = 1 To Len(txt)
c = Mid(txt, i, 1)
If c Like "[A-Za-z]" Then cnt = cnt + 1
Next i
CountLettersOnly = cnt
End Function➧ If c Like "A-Za-z" Then cnt = cnt + 1: This is the main logic. For each character (c) in the string, it checks if it is a letter (uppercase or lowercase) using the Like operator and the pattern "A-Za-z". If it is a letter, a counter (cnt) is increased by one.
➤ Coming back to the worksheet, choose a cell and write the formula below.
=CountLettersOnly(B2)
➤ Press ENTER and drag down the Fill Handle.
The result will show the total count of only the letters in the string, excluding all the other characters.
Frequently Asked Questions
Is there a way to automatically update character counts when cell values change?
Yes, if you use a user-defined function, it will recalculate whenever the worksheet changes. For macros, you might need to re-run the macro or use a Worksheet_Change event.
What’s the difference between using LEN in Excel formulas and using LEN in VBA?
Both work the same way the LEN function count characters. The difference is that VBA lets you automate the process across multiple ranges, add conditions (like ignoring spaces), or create custom logic (like counting only letters).
Can I count characters across the whole worksheet instead of one cell?
Yes, you can write a loop that goes through each used cell (For Each cell In UsedRange) and sum up the lengths using LEN function. This will give you the total character count across the sheet.
Concluding Words
Above, we have explored different ways to count the number of characters in a string using VBA. Whether you need to find the total character count, exclude spaces, count specific characters, or count only the letters, these methods will make work easy for you. If you have any further questions, feel free to share them below.
