Updated July 12, 2023
Count Characters in Excel
The count characters feature helps determine the number of characters present in a cell or range of cells. In Microsoft Excel, you can calculate the total number of characters in a text string, i.e., the length of the specified string in a cell, using the LEN (Length) function.
LEN function counts characters in a cell, including punctuation, numbers, special symbols, and space delimiter. The count characters feature of Excel helps set a limit on data entered in a cell, validating data entry and text analysis.
The syntax for the LEN function:
In “=LEN(text)”, the text refers to the cell value you want to count the characters.
How to Count Characters in Excel?
You can count characters in Excel using functions like LEN, SUBSTITUTE, SUM, and SUMPRODUCT. We have included various examples below to illustrate their usage.
To better understand how these functions work, please download the template below and keep it open while reviewing the examples.
Example #1
Count Characters in a Cell
Consider the below data set of random text samples. Here, you must find out the total number of characters in each cell.
Solution:
Step 1: Select Cell B2 and enter the formula:
Step 2: Press” Enter“.
Result “4″ is displayed in Cell B2, as shown below.
Step 3: Drag the cell downwards.
The LEN function returns “13” for the text in cell A3, whereas the actual character count is “12“; see the below image.
Example #2
Count Characters Without Spaces
This method will teach you to count characters in a cell, excluding spaces, by combining the “LEN” and “SUBSTITUTE” functions. This combination will exclude areas from the text and give a text’s exact total characters.
Let us revisit the previous example where the result is inclusive of spaces. Now, we want to determine the total number of characters without spaces.
Solution:
Step 1: Select Cell C2 and enter the formula:
Explanation of the formula:=LEN(SUBSTITUTE(A2,” “,””))
The SUBSTITUTE function will replace the space character (““) in Cell A2 with empty text (“). So, the formula won’t count the spaces.
Step 2: Press “Enter“.
The output of the function is “4“, as shown below.
Step 3: Drag the cell downwards.
The above image shows the differences in the total characters with and without spaces.
Let’s understand how the formula works in Cell C3:
When the formula =LEN(SUBSTITUTE(A3,” “,”)) is applied in Cell B3, the SUBSTITUTE function replaces the space character (““) in Cell B3 with empty text (“). So, the existing text “Martin Chapel” will be read as “Martin Chapel“. The LEN function will count the total character in a cell. Thus, the output is “12″ instead of 13.
Example #3
Count Total Characters in a Range
The total number of characters in a row or column (or cell range) can be counted using two methods:
- Combination of SUM and LEN functions
- Combination of SUMPRODUCT and LEN functions.
Syntax:
- =SUM(LEN(cell range))
- =SUMPRODUCT(LEN(cell range))
Consider the below example; here, we will calculate the total number of characters in column A using two combinations of functions. First, we will use the SUM and LEN functions, and then the SUMPRODUCT and LEN functions.
Solution:
1. Calculate Total Characters using LEN and SUM
Step 1: Select Cell C2 and enter the formula as shown below:
Explanation of the formula: =SUM(LEN(A2:A6))
- The “LEN” function will calculate the character count of the text string for each cell in the given range (A2:A6) and returns an array of numbers, i.e. (4,13,11,9,22).
- After then, the “SUM” function adds all numbers and returns the total character count, i.e., 59.
Step 2: Press “Enter”.
The result “59” is displayed in Cell C2.
2. Calculate Total Characters using LEN and SUMPRODUCT
Step 1: Select Cell D2 and enter the formula:
Step 2: Press “Enter”.
The function will display “59” in Cell D2.
Example #4
Count Special Characters in a Cell
Sometimes, your data may contain certain letters, numbers, or special characters like *, @, #, &, or other delimiters. You can retrieve the count of those characters using the LEN and SUBSTITUTE functions.
For example, you have the following text data with special characters in each cell. You want to know the total number of asterisks (*) in each cell.
Solution:
Step 1: Select Cell B2 and enter the formula:
Explanation of formula =LEN(A2)-LEN(SUBSTITUTE(A2,”*”,””))
- The “LEN(A2)” function will give the total number of characters in Cell A2, i.e., 7
- In “LEN(SUBSTITUTE(A2, “*”,””))”, the SUBSTITUTE function will replace “*” in Cell A2 with an empty string. Thus, “LEN(SUBSTITUTE(A2, “*”,””))” equals 5. It is the length of a text string without “*“.
- This formula “=LEN(A2)-LEN(SUBSTITUTE(A2, “*”,””))” will subtract 7 from 5 and will give 2. The output is the number of character “*” occurrences in Cell A2.
Step 2: Press “Enter“.
The function will display 2 in Cell B2, as shown below.
Step 3: Drag Cell B2 downwards.
Example #5
Count Specific Sensitive/Insensitive Characters in a Cell
In this method, you will learn to calculate specific sensitive/insensitive characters in a cell using LEN and SUBSTITUTE functions.
Consider the below data of some random sentences. Here you want to know the count of letters A/a using LEN and SUBSTITUTE functions. You want the function to give the total count of A and a, considering both uppercase and lowercase.
Solution:
Let’s understand the case-sensitive nature of the SUBSTITUTE function using three different cases.
Case 1: Count Character “A” from the Given Data
Step 1: Select Cell B2 and enter the formula:
Step 2: Press “Enter“.
Step 3: Drag the cell downwards.
Case 2: Count Character “a” from the Given Data
Step 1: Select Cell C2 and enter the formula:
Step 2: Press “Enter“.
Step 3: Drag the cell downwards.
You must have understood the case-sensitive nature of the SUBSTITUTE function. Now, let’s calculate the total count of A and a, considering both uppercase and lowercase.
Case 3: Count the Letters “A” and “a” from the Data
Step 1: Select Cell D2 and enter the formula:
Step 2: Press “Enter“.
Step 3: Drag the cell downwards.
Note: You can also use the below formulas to count sensitive/insensitive characters.
- To convert any lowercase “a” to uppercase “A” and then count all occurrences of the letter “A” you can use this formula:
=LEN(A2)–LEN(SUBSTITUTE(UPPER(A2), “A”, “”))
- To convert any uppercase “A” to lowercase “a” and then count all occurrences of the letter “a” you can use this formula:
=LEN(A2)–LEN(SUBSTITUTE(LOWER(A2), “a”, “”))
- To add a character count of uppercase “A” and lowercase “a”, you can use this formula:
=LEN(A2)–LEN(SUBSTITUTE(A2,”A”,””))+LEN(A2)–LEN(SUBSTITUTE(A2,”a”,””))
Example #6
Count Characters Before and After Decimal Point
Suppose you have the dataset below and want to count characters or digits before and after the decimal point.
Solution:
1. Count Characters/Numbers before the Decimal
Step 1: Select Cell B2 and enter the formula:
Step 2: Press “Enter”.
The function will display 5.
Step 3: Drag the cell with the formula downwards.
2. Count Characters/Numbers after the Decimal
Step 1: Select Cell C2 and enter the formula:
Step 2: Press “Enter”.
The function will display 3.
Step 3: Drag the cell with the formula downwards.
These are a few examples of Excel’s LEN function to count characters.
Things to Remember To Count Characters in Excel
- LEN function counts every character in a cell, including punctuation marks, special characters, numbers, and space characters (trailing, leading, and double spaces between characters). It may sometimes give you incorrect or wrong results.
- Use the SUM or SUMPRODUCT function to calculate the number of characters in a range of cells.
- Use Excel’s LEN and SUBSTITUTE functions to determine the total characters in a cell without spaces.
- The TRIM function can also be used to count characters by excluding spaces.
- The SUBSTITUTE function is a case-sensitive function.
- Use the UPPER/LOWER function for counting characters without case-sensitive criteria.
Frequently Asked Questions(FAQs)
Q1. What is the character limit for cells in Excel?
Answer: The character limit for each Microsoft Excel cell is 32,767.
Q2. How to count words in Excel?
Answer: There is no in-built function in Excel to count words in Excel. However, you can use the below formula for counting words in a cell.
Q3. What is the keyboard shortcut for character count?
Answer: The keyboard shortcut to instantly display Excel’s Word Count dialog box is by pressing Ctrl + Shift + G keys.
Recommended Articles
This article provides a step-by-step guide on how to count characters in Excel using various methods with practical examples. We have also provided a downloadable Excel template for better understanding. To understand more about Excel features, you can also read our other articles.
