EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Excel Tips Count Characters in Excel
 

Count Characters in Excel

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated July 12, 2023

Count Characters in Excel

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The syntax for the LEN function:
Count Characters example 3-10

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.

You can download this Count-Characters-Excel-Template here – Count-Characters-Excel-Template

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.
Example 1

Solution:
Step 1: Select Cell B2 and enter the formula:

=LEN(A2)

Example Step 1

Step 2: Press” Enter“.
Result “4″ is displayed in Cell B2, as shown below.
Step 3: Drag the cell downwards.
Example 1 Step 3

Result: Total number of characters in each cell is successfully calculated using the “LEN” function, as shown below.

Example 1 Step 3 -2

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 1 Step 3-3

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,” “,””))

Example 2 Step 1

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.
Example 2 Step 3

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

Example 2 Step 3-2

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.
Example 3

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))

Count Characters in Excel-Example 3 Step 1

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.
Example 3 Step 2

2. Calculate Total Characters using LEN and SUMPRODUCT

Step 1: Select Cell D2 and enter the formula:

=SUMPRODUCT(LEN(A2:A6))

Example 3 2 Step 1

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.

Count Characters in Excel-Example 3 2 Step 2

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.
Example 4

Solution:
Step 1: Select Cell B2 and enter the formula:

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

Count Characters in Excel-Example 4 Step 1

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 4 Step 3

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

Count Characters in Excel-Example 4 Step 3-2

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.
Example 5

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.
Count Characters in Excel-Example 5 Step 3

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

Example 5 Step 3-2

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.
Count Characters in Excel-Example 5 case 2 Step 3

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

Example 5 case 2 Step 3-2

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.
Example 5 Case 3 Step 3

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

Count Characters in Excel-Example 5 Case 3 Step 3-2

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.
Example 6

Solution:

1. Count Characters/Numbers before the Decimal

Step 1: Select Cell B2 and enter the formula:

=LEN(INT(A2))

Count Characters in Excel-Example 6 1 Step 1

Step 2: Press “Enter”.
The function will display 5.
Step 3: Drag the cell with the formula downwards.
Count Characters in Excel-Example 6 1 Step 3

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

Example 6 1 Step 3-2

2. Count Characters/Numbers after the Decimal

Step 1: Select Cell C2 and enter the formula:

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

Step 2: Press “Enter”.
Example 6 2 Step 2

The function will display 3.
Step 3: Drag the cell with the formula downwards.
Count Characters in Excel-Example 6 2 Step 3

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

Count Characters in Excel-Example 6 2 Step 3-2

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.

  1. LOOKUP in Excel
  2. Excel Row Count
  3. DAY Formula in Excel
  4. Sparklines in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Count-Characters-Excel-Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Count-Characters-Excel-Template

EDUCBA

डाउनलोड Count-Characters-Excel-Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW