Count Characters Using Excel Formulas in a Cell (or Range of Cells)

 

You may use Excel’s fantastic text features to your advantage when working with text data.

You might occasionally need to figure out the overall number of characters in a cell or range or the frequency of a particular character.

While the LEN function can count the characters in a cell, the remainder may be accomplished using a mix of formulae (as we will see later in the examples).

I’ll go through many examples in this lesson of how to count all or a certain number of characters in an Excel column or range.

This instruction explains: 1. Count Each Character in a Cell 2. All Characters in a Range of Cells Are To Be Counted 3. Count a Cell's Specific Characters 4. Count the Numbers/Characters Before and After the Decimal

1. Count Each Character in a Cell

The LEN function may be used to easily calculate the total number of characters in a cell.

The text enclosed in double quotes or the cell reference to a cell that contains the text may be used as the only argument for the LEN function.

Consider the following dataset as an example, and imagine you want to count the total amount of characters in each cell:

The equation to accomplish this is given below:

=LEN(A2)

The LEN function may not seem like much when used alone, but when combined with other formulae, it may provide some amazing results (such as getting the word count in a cell or splitting first and last names).

It should be noted that the LEN function counts all characters in a cell, including special characters, digits, punctuation marks, and space characters (leading, trailing, and double spaces between words).

Since the LEN function counts every character in a cell, you occasionally risk getting the incorrect answer if the cell contains additional spaces.

For instance, in the example below, the text in cell A1 should have returned 22 instead of 25, however, the LEN function instead gives 25. However, since it also counts additional space characters, the outcome is incorrect.

You can 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 unnecessary spaces from being tallied.

This is what the formula below does:

=LEN(TRIM(A2))

 

2. All Characters in a Range of Cells Are To Be Counted

The LEN function may also be used to calculate the total number of characters in a given range.

Consider the following dataset: this time, I’m interested in the total amount of characters, not just the number of characters in each cell.

Utilizing the following formula, you may achieve that:

=SUMPRODUCT(LEN(A2:A7)))

Let me describe the operation of this formula.

The LEN portion of the function in the formula above counts the characters in each cell using the complete range of cells.

The LEN function’s output would be:

{22;21;23;23;23;31}

The character count in the cell is shown by each of these values.

And when you combine all of these values using the SUMPRODUCT function, it would just sum them.

If you’re wondering why you can’t use SUM instead of SUMPRODUCT, it’s because this is an array, and the SUM function cannot handle arrays but SUMPRODUCT can.

However, if you still want to utilize SUM, you may do so with the following formula (only remember to enter the result using Control + Shift + Enter rather than just Enter).

=SUM(LEN(A2:A7))

3. Count a Cell’s Specific Characters

As I previously stated, the LEN function is most useful when used with other formulae.

Additionally, you may use a formula combination to count certain characters in a cell, such as letters, numbers, special characters, or spaces.

Consider the following dataset as an example. Suppose you want to count the total amount of words in each cell.

Although there isn’t a built-in algorithm to get the word count, you may count the space characters and use that information to determine how many words are in all of the cells.

You may calculate the total amount of space characters in a cell using the formula below:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1

The word count is calculated using the aforementioned formula by adding 1 to the total number of space characters.

This formula works as follows:

1. All of the space characters are replaced with blanks using the SUBSTITUTE function. When there are no space characters, the LEN function is utilized to determine the overall number of characters.
2. LEN is subsequently reduced by the LEN(SUBSTITUTE(A2″ “,””)) result (A2). This tells us how many space characters are there overall in the cell.
3. The formula adds 1, thus there will be words then there are characters for spaces overall (as two words are separated with one character).

Keep in mind that if there are any leading, trailing, or double spaces, the outcome will be incorrect. The TRIM function and the LEN function are the two to employ in this situation.

The same reasoning may be used to locate a particular character, word, or phrase within a cell.

For instance, let’s say I have a dataset like the one below with several batches, each of which is represented by an alphabet and a number (such as A1, J2, etc.)

The following equation will tell you how many times a batch beginning with the letter A has been manufactured overall each month:

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

The rationale behind the above formula is the same as before: measure the length of the text in a cell with and without the character you wish to count, and then subtract the two.

The character that I want to count is hard coded in the formula above, but you can also place it in a cell and then use the cell reference. This makes it more practical because the formula would update the next time the text in the cell was changed.

Use a Case-Insensitive Formula to Count Specific Characters

The formula used to count certain characters in a cell has one flaw.

Case distinction applies to the SUBSTITUTE function. This proves that “A” and “a” are not equivalent. This is why cell C5 displays the incorrect result (the result should have been 3).

How then can you determine a character’s character count when it might have been in any case? (lower or upper).

Making your formula case-insensitive helps you do this. Although you may use a complicated formula, I just added the number of characters in both circumstances (lowercase and uppercase A).

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))+LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))

4. Count the Numbers/Characters Before and After the Decimal

This is a frequent question I receive from my readers and have seen in other places, including this one. I have no idea why.

Let’s say you want to count the characters before and after the decimal in the dataset that is depicted below.

The formulae for doing this are listed below.

Count the letters or digits before the decimal point:

=LEN(INT(A2))

Count the letters or digits after the decimal:

=LEN(A2)-FIND(".",A2)

Keep in mind that these formulae are only intended for the cell’s significant digits. The methods above would still give you significant digits before and after the decimal even if you had leading or trailing zeroes or had used custom number formatting to show more/fewer numbers.

In Excel, you can use formulae to count characters in a cell or a range of cells in a few different situations.

I sincerely hope the instruction was helpful.

ms excel vba