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:

**=LEN(A2)**

**Step 2:** Press” **Enter**“.

Result “**4″ **is displayed in **Cell B2**, as shown below.

**Step 3:** Drag the cell downwards.

**Result:**Total number of characters in each cell is successfully calculated using the “

**LEN”**function, as shown below.

**Note:**The

**LEN**function will consider space as a character and may give the wrong results.

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:

**=LEN(SUBSTITUTE(A2,” “,””))**

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.

**Result:**The functions displayed the total number of characters in a cell without spaces.

The above image shows the differences in the total characters with and without spaces.

**Note:**The total count of characters in Cell C3 is 12 (instead of 13 in the previous example) because of the “

**SUBSTITUTE”**function.

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:

**=SUM(LEN(A2:A6))**

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:

**SUMPRODUCT(LEN(A2:A6))**

**Step 2: **Press **“Enter”.**

The function will display “**59**” in **Cell D2**.

**Result:**You have successfully calculated the total characters of given data using “

**LEN”**with “

**SUM”**and “

**SUMPRODUCT”**functions.

### 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:

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

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.

**Result:**The formula has calculated the number of asterisks(*) from the given data.

### 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.

**Note**: The

**SUBSTITUTE**function in Excel is case-sensitive and treats lowercase and uppercase letters as different characters. It means “A” is not equal to “a”.

**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:

**=LEN(A2)–LEN(SUBSTITUTE(A2,”A”,””))**

**Step 2:** Press “**Enter**“.

**Step 3: **Drag the cell downwards.

**Result:**The function will display the count of the letter A in uppercase only.

##### Case 2: Count Character “a” from the Given Data

**Step 1: **Select **Cell C2** and enter the formula:

**=LEN(A2)–LEN(SUBSTITUTE(A2,”a”,””))**

**Step 2:** Press “**Enter**“.

**Step 3: **Drag the cell downwards.

**Result:**The function will display the count of the letter “a” in lowercase.

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:

**=LEN(A2)–LEN(SUBSTITUTE(SUBSTITUTE(A2, “A”,””), “a”,””))**

**Step 2:** Press “**Enter**“.

**Step 3: **Drag the cell downwards.

**Result:**The function will display the total count of the letter “A” and “a”.

**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:

**=LEN(INT(A2))**

**Step 2: **Press** “Enter”. **

The function will display **5**.

**Step 3: **Drag the cell with the formula downwards.

**Result:**The formula displayed the count of digits before the decimal point.

##### 2. Count Characters/Numbers after the Decimal

**Step 1: **Select **Cell C2 **and enter the formula:

**=LEN(A2)–FIND(“.”,A2)**

**Step 2: **Press **“Enter”.**

The function will display **3**.

**Step 3: **Drag the cell with the formula downwards.

**Result:**The formula displayed the count of digits after the decimal point.

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.

**=LEN(TRIM(**

*Cell*))–LEN(SUBSTITUTE(*Cell*,” “,””))+1**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.