Count Characters in Excel

Count Characters In An Excel Cell

When working with Excel for financial analysis, you may have to count the characters in a cell or a range of cells. Excel provides an easy option, the LEN function, for this purpose. The LEN function calculates the number of characters in a cell, including spaces, punctuations, letters, and special characters.

In the example below, we have a string in cell A2, and we must count the number of characters in it. Use the formula =LEN(A2) in cell B2 to find the number of characters in the string. You get a result of 28, which includes the 21 letters, three punctuations, and four blank spaces in the text string.

Count Characters in an Excel Cell

We use the LEN function to count characters in Excel in a single cell. Combined with functions like SUM, SUMPRODUCT, and SUBSTITUTE, it can perform more complex types of counting.
The LEN function counts all the characters in a cell, including alphabets, numbers, special characters, and spaces. It takes a text string or a cell reference as an argument.
If a cell is empty or we supply an empty string as an argument, the LEN function returns a zero (0).

How To Count Characters In Excel?

You may have requirements to count characters in a single cell, a range of cells, or even specific characters. Let us look at how we can use the LEN Excel function in various ways.

To apply the LEN function, you must type it in the specific cell where you need the result. For example, in cell B2 below, you need the count of the characters in cell A2. So, type =LEN(A2) in cell B2 and press Enter.

To count characters in Excel shortcut method, you may select the formula from the Excel ribbon. Then, place the cursor in the cell you want the result in and choose the LEN function through the Excel ribbon.

Formulas Tab -> Text option in the Function Library group -> LEN

As seen above, the simplest way to count characters in Excel in a single cell is through the LEN Excel function. Note that you may supply the string argument directly within double quotes or use the cell reference containing the string as an argument.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Let us look at some examples to understand how you can count the characters in Excel in a specific cell or a range of cells.

Example #1 – Count all Characters in Each Cell of a Column

Below is a table containing the email IDs of some people working in different organizations in Column A. We should count the number of characters for each cell from A2 to A6.

Step 1: Use the following formula in cell B2 to count characters in Excel.

=LEN(A2)

Step 2: Press Enter. You get the number of characters in cell A2, including the special character “@” and period (.).

Step 3: Drag the Autofill handle from B2 to B7 to get the total number of characters in each cell from A2 to A6.

Count Characters in Specific Cells

Suppose you need the total count of the characters in cells A2, A4, and A6. Then, you can use the LEN function along with the SUM function.

To count the characters in specific cells, you may use the SUM function to add the total characters in each cell.

=SUM(LEN(A2),LEN(A4),LEN(A6))

Type the above formula in cell B8 as shown below and press Enter. You get a result of 48, which is the total number of characters in cells A2, A4, and A6.

Thus, the characters in each cell, or group of cells, can be counted using LEN.

Example #2 – Count the Characters Left in a Cell After Excluding a Specific Character

To count the specific characters in a cell and exclude it from the final count, you should use a combination of SUBSTITUTE Excel function and LEN functions.

SUBSTITUTE Function

The SUBSTITUTE function replaces a particular text with a new one in a string.
The syntax of the SUBSTITUTE function is =SUBSTITUTE(cell reference/text, old text, new text, [instance_num]).

Let us look at an example where you must count the number of characters in a cell after excluding a specific character. Below is a text string in cell A2. We must count characters in Excel cell A2 after excluding a particular character, “P.”

Step 1: Let us exclude the letter P and count the remaining characters. For this, use the following formula in cell B2.

=LEN(SUBSTITUTE(A2,”P”,””))

Step 2: Press Enter. You get a count of the characters in cell A2, excluding the letter “P.”

Here, the formula has calculated the total number of characters, including the spaces and exclamation.

Explanation: The SUBSTITUTE function above finds the letter “P” and substitutes it with the value mentioned in the following argument. We have just given two quotes without space (“”). Therefore, it removes the character “P,” and the LEN function counts the rest of the characters.

However, the SUBSTITUTE function is case-sensitive, and you will notice that it has not removed the lower-case “p” from the count. To make the substitution case insensitive, use the UPPER or LOWER Excel function to convert the entire text to upper or lower case. In this case, we use UPPER.

=LEN(SUBSTITUTE(UPPER(A2),”P”, “”))

Type the following function in cell B3 and Press Enter.

The function excludes both the “Ps” and counts the remaining characters. Thus, use the above formula to count characters in Excel, excluding a specific character.

Example #3 – Count the Occurrence of a Specific Character in a Cell

We have seen how to count the specific characters in a cell, excluding a character. Now, let us look at how to count a specific character in a cell. Below we have a text string in cell A2.

Step 1: Here, we must find the count of “o” in this string. To do so, use the following formula in cell B2.

=(LEN(A2) – LEN(SUBSTITUTE(A2,”o”,””)))

SUBSTITUTE(A2, “o”, “”)) removes the letter “o” from the text string since it is being replaced by “”
The part LEN(SUBSTITUTE(A2, “o”, “”)) counts all the characters in the string after removing the letter “o.”
Hence, to find the number of occurrences of “o,” we subtract this result from the total length of the string, given by =LEN(A2).

Step 2: After entering the formula in B2, press Enter. You get the character “o” count in the text string, which is 4.

Thus, we can count the occurrence of any character in a string. To calculate character occurrences in multiple cells, you must use the cell range in the formula as shown in the Formula Bar below.

You get the total count of “o” in cells A2 to A4 by specifying the range using SUMPRODUCT.

Example #4 – Count all Characters of a Vertical Range

Let us look at an example where you may need to count all the characters in a vertical range. For this purpose, you may use the versatile SUMPRODUCT excel function. The SUMPRODUCT function adds up arrays or a range of cells and returns its total. Below is a table that contains a list of names in Column A.

Step 1: Now, you may use the formula to find the total number of characters in Column A from cells A2 to A9.

=SUMPRODUCT(LEN(A2:A9))

Here, the syntax of the SUMPRODUCT function is SUMPRODUCT(array1, array2….). The function will add up all the arrays listed. Now, apply the formula in cell B11.

Step 2: Press Enter. You get the total count of all the characters in the cells from A2 to A9, including blank spaces.

Step 3: To cross-verify the same, you can apply the =LEN(A2) function in cell B2 and drag the Autofill handle up to B9.

Now, find the sum of the numbers in cells B2:B9 using the SUM function. You get the same result as when using SUMPRODUCT.

Thus, the SUMPRODUCT function helps count the characters in a vertical range with ease.

Removing Leading And Trailing Spaces using TRIM Function With LEN

When you count characters in Excel, you can always use the TRIM function to remove leading or trailing blank spaces.

For example, in the table below, if you use =LEN(A2) in cell B2 to determine the characters in A2, you get 15, which includes even the leading and middle blank spaces. The LEN function is sufficient to count characters in Excel before space.

If you use the formula =LEN(TRIM(A3)) in cell B3, you get the result 11, which indicates the removal of the leading spaces. However, the middle blank space remains.

Count Characters in Excel - Removing Leading and Trailing Spaces - 1

Removing Leading and Trailing Spaces - 2

Important Things To Note

The LEN function used to find the characters in a single cell takes only one argument.
To count both the upper- and lower-case occurrences of a particular character in a cell, use the LOWER or UPPER function, with SUBSTITUTE and LEN, to get the correct count.
LEN does not consider number formatting to count characters in Excel. For example, for $ 1,000 in a cell, LEN returns 4 when it is formatted as currency.
Use the SUM function with LEN to count the characters in multiple cells.
To count characters in Excel in a range of cells, use the SUMPRODUCT function.
Use the TRIM and LEN functions to remove the leading or trailing blank spaces when you count characters in Excel.

Frequently Asked Questions (FAQs)

1. How to count characters in excel including spaces?

To count characters in Excel, including spaces, you should use the LEN Excel function with the syntax =LEN(text). It also includes punctuation, numbers, and special characters in the count. Here, spaces mean both the space between words in a string and leading and trailing spaces.

2. How to count characters in excel without spaces?

The TRIM Excel function can remove leading and trailing spaces and count characters. You can also use the SUBSTITUTE function to remove space between the words in the following way:
=SUBSTITUTE(cell_reference, “ “,””).

3. Why is count characters in excel not working?

When used to count the characters in an empty cell, the LEN Excel function will return zero. Also, if the cell used to enter the LEN function is formatted as text, the formula will not work. Besides, the function does not include number formatting to count characters in Excel cells.

4. How to count characters in excel multiple cells?

To count characters in multiple cells, you can use the SUM or SUMPRODUCT function along with the LEN function.

Download Template

This article must help understand Count Characters in Excel, formulas and examples. You can download the template here to use it instantly.

Count Characters in Excel Download

Count Characters in Excel