To count all characters in a cell, including letters, numbers, special characters, and all spaces, use Excel’s LEN function. You must utilize the LEN function in Excel to obtain a character count, unlike Word, where you may do so quickly and conveniently utilizing a tool in the menu bar.
When to Use the LEN Function in Excel?
You may employ the LEN function to calculate the total number of characters in a given string. This is beneficial if you want to figure out how long a string is in a certain cell.
Character count in excel single cell:
The Excel LEN function counts each and every character in a given cell, including all leading, trailing, and inter-word spaces.
For example, to get the length of cell A2, showing in the below screenshot, use this formula:
=Len(A2)
The LEN algorithm identified 18 characters in cell A2, containing 15 letters and 3 spaces.
How to Count the character of a single cell?
Step-1: Prepare a data table like below:
Step-2: Set a formula in cell B-2, outlined below:
Result outlined below:
Excel has character restrictions. The header, for example, is limited to 254 characters. The header will be removed if one goes over the allowed amount.
When you have really long strings in your cells and need to make sure that they don’t exceed 254 characters to prevent issues with importing or displaying, the formula can be useful.
Excel character count – without leading and trailing spaces:
Leading or trailing spaces, or excess spaces at the start or end of the elements, are a common issue when working with large worksheets. Use the TRIM function to remove any leading, trailing, or double spaces before using the LEN function to obtain the actual word count to prevent excess spaces from being counted.
As shown in the below, the cell A2 & A3 contains 22 & 40 characters as it holds some extra spaces.
But in this below example, the cell A2 & A3 shows 18 & 33 characters without extra spaces.
How to count character without leading and trailing spaces?
To count character without leading and trailing spaces follow the below process.
Simply include the TRIM function in the Excel LEN formula to extract the string length without leading and trailing spaces:
The following Formula can be used:
=LEN(TRIM(A2))
Step-1: Select the data table with information outlined below:
Step-2: Put the formula in cell B-2 cell, outlined below:
Result outlined below:
Drag the formula to the below cell to continue:
How to get the Character counts of multiple cells?
The first thing that springs to mind when trying to figure out how many characters there are in all the cells, is to count the characters in each cell individually.
The simplest method to determine the total number of characters across multiple cells is to sum up various LEN functions, for instance:
=LEN(A2)+LEN(A3)
Or, to sum the character counts returned by LEN formulae, use the SUM function:
=SUM(LEN(A2),LEN(A3))
In either case, the formula calculates the overall string length by counting the characters in each of the designated cells.
Character counts of multiple cells:
Step-1: Prepare a data table with information outlined below:
Step-2: Set a formula in cell C-2, outlined below:
Result outlined below:
Example-2 Second Formulas:
- Select Cell C2 outline in Step 1
- Put the Formula below:
=SUM(LEN(A2),LEN(A3))
- Press Enter
How to get the count of a specific character in a cell?
Sometimes you only need to count occurrences of a certain letter, number, or special symbol within a cell rather than all the characters that appear within it. Excel has the option to count the frequency of a particular character in addition to measuring the total number of characters.
Use the formula below to count the occurrences of a single character in a range:
=LEN(cell)-LEN(SUBSTITUTE(cell, “character”,””))
In a specific example, if you want to count the number of character “u” in a defined cell A2, the formula will look like:
=LEN(A2)-LEN(SUBSTITUTE(A2,”u”,””))
Step-1: Set a formula in cell B-2, outlined below:
Result outlined below
Drag the formula to the below cell to continue:
How to count characters/digits before decimal?
Step-1: Prepare a data table with information outlined below:
Step-2: Set a formula in cell B-2 to count the letters or digits before the decimal point outlined below:
Formula for before decimal:
=LEN(INT(A2))
Step-3: Position the cursor to the lower right corner of the cell B2, wait until it turns into the plus sign, and then double-click the plus, outlined below:
Result outlined below
How to count characters/digits after decimal?
Count the letters or digits after the decimal point:
=LEN(cell)-FIND(“.”,cell)
So, if you want to count the character AFTER the decimal of cell A3, the formula will look like:
=LEN(A3)-FIND(“.”,A3) Steps to Count characters/digits after decimal:
Step-1: Prepare a data table with information outlined below:
Step-2: Set a formula in cell B-2 and press enter, outlined below:
=LEN(A2)-FIND(“.”,A2)
Step-3: Position the cursor to the lower right corner of the cell B2, wait until it turns into thick black cross. which is called the Fill handle.
- Hold and drag the fill handle down the column over the cells where you want to copy the formula. Drag B2 to B-7, outlined below:
You may be interested:
