Trending September 2023 # Learn How To Count Characters In Excel With Examples # Suggested October 2023 # Top 10 Popular

You are reading the article Learn How To Count Characters In Excel With Examples updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Learn How To Count Characters In Excel With Examples

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.

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

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

Step 1: Select Cell B2 and enter the formula:

LEN(A2)

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.

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.

Step 1: Select Cell C2 and enter 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.

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.

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

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.

2. Calculate Total Characters using LEN and SUMPRODUCT

Step 1: Select Cell D2 and enter the formula:

SUMPRODUCT(

LEN(A2:A6)

)

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.

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.

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

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

)

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

)

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

)

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

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

Solution:

1. Count Characters/Numbers before the Decimal

Step 1: Select Cell B2 and enter the formula:

LEN(

INT(A2)

)

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

)

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)

Answer: The character limit for each Microsoft Excel cell is 32,767.

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

)

Answer: The keyboard shortcut to instantly display Excel’s Word Count dialog box is by pressing Ctrl + Shift + G keys.