Count Symbols in Excel

Introduction to Counting Symbols in Excel

When working with Excel, it’s common to encounter cells that contain a mix of numbers, letters, and symbols. In some cases, you might need to count the number of symbols in a cell or a range of cells. This can be useful for data analysis, data cleaning, or simply to understand the composition of your data. In this article, we’ll explore how to count symbols in Excel using various methods.

Using Formulas to Count Symbols

One of the most straightforward ways to count symbols in Excel is by using formulas. The LEN function, which returns the length of a text string, can be combined with other functions to achieve this. However, to specifically count symbols, we often use a combination of the LEN function and the SUBSTITUTE function, which replaces text with other text.

For example, to count the number of symbols in a cell A1, excluding letters and numbers, you can use a formula that looks something like this:

Formula	Description
=LEN(A1)-LEN(SUBSTITUTE(A1,"*",""))	This formula counts the asterisks (\*) in cell A1.
=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))	This formula counts the spaces in cell A1.

However, these formulas are limited to counting a specific symbol at a time. To count all symbols, you would need a formula that can differentiate between alphanumeric characters and everything else.

Counting All Symbols

To count all symbols in a cell, you can use an array formula that iterates through each character in the cell and checks if it’s not a letter or a number. Here’s how you can do it:

The formula to count all non-alphanumeric characters (symbols) in cell A1 is:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("1:255"))),"")))

This formula works by substituting every possible character code (from 1 to 255) into the cell and seeing if it reduces the length. If it does, that character is present in the cell. However, this method is cumbersome and doesn't directly tell you how many symbols are there in total without also considering each symbol individually.

A more direct approach to count all symbols (non-alphanumeric characters) in a cell A1 can be achieved with the following formula:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122))

This formula checks each character in the cell to see if its ASCII code falls outside the ranges for letters and numbers, thus identifying symbols.

Using VBA to Count Symbols

Another way to count symbols in Excel is by using Visual Basic for Applications (VBA). You can create a custom function that iterates through each character in a string and checks if it’s a symbol.

The VBA function to count symbols could look something like this:

Function CountSymbols(text As String) As Long
    Dim i As Long
    For i = 1 To Len(text)
        If Not Chr(Asc(Mid(text, i, 1))) Like "[a-zA-Z0-9]" Then
            CountSymbols = CountSymbols + 1
        End If
    Next i
End Function

This function goes through each character in the input string, checks if it's not a letter or a number using a regular expression-like pattern, and increments the counter if it's a symbol.

📝 Note: To use this VBA function, you need to open the Visual Basic Editor in Excel (press Alt + F11), insert a new module (right-click any of the objects for your workbook in the Project Explorer, then Insert > Module), and paste the function into the module window.

Conclusion

Counting symbols in Excel can be achieved through various methods, including using formulas, array formulas, and VBA. The choice of method depends on your specific needs and how you want to approach the task. Whether you’re dealing with a simple count of a specific symbol or need to tally all non-alphanumeric characters in a cell or range, there’s a solution available. By mastering these techniques, you can enhance your data analysis capabilities and better understand the composition of your data in Excel.