Count the Number of Characters in a Cell in Google Sheets (LEN Function)

If you work with text data in Google Sheets, sometimes you may have a need to know the character count in a cell in Google Sheets.

I have faced similar situations when I am writing titles for an article and they need to be of a specific character.

In that case, I can quickly put these titles in Google Sheets and use formulas to get the character count.

In this tutorial, I will show you how to count the number of characters in a cell/range in Google Sheets using the LEN function.

So let’s get started!

Count Characters in a Cell in Google Sheets

This is pretty easy – after all, Google Sheets has a dedicated function to count characters in a cell.

The LEN function.

Suppose you have a dataset as shown below:

Data to count characters in Google Sheets

Below is the formula that will count the number of characters in each cell:

=LEN(B2)

Count Characters uisng the LEN function

Note that the LEN function counts every character – be it an alphabet, number, special character, punctuation marks, or space characters.

While this works well in most cases, sometimes you may get a wrong result due to leading, trailing, or double spaces in a cell.

For example, in the below example, the LEN function gives the result in cell B6 as 27 (instead of 25) because there is a leading space and a double space.

Wrong Result because of double spaces and leading space

This happens because the LEN function counts all characters (including the space character).

To make sure you’re not counting extra spaces, you can use the TRIM function along with the LEN function.

Below is the formula that will ignore any leading, trailing, or double spaces in Google Sheets:

=LEN(TRIM(A2))

Count Characters in a Range in Google Sheets

You can also count the total number of characters in a range of cells.

For example, suppose you have the below dataset and you want to count the total number of characters in cell A2:A6

Data to count characters in Google Sheets

Below is the formula that will do this:

=SUMPRODUCT(LEN(A2:A6))

Count all the characters in a range in Google Sheets

In the above formula, SUMPRODUCT is used to give you the sum of all the character count in the entire range (which is done by the LEN function).

You can also do the same thing using the below SUM function:

=SUM(LEN(A2:A6))

But since the SUM function can not handle arrays, you will have to use Control + Shift + Enter instead of the normal Enter (hold the Control and the Shift key and then hit the Enter/Return key).

Or even better, use the below formula:

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

Count Occurrence of a Specific Character in Google Sheets

You can also count the occurrence of a specific character (or a string in a cell) using formulas.

For example, suppose you want to know how many times the alphabet “a” has been used in each movie name,

You can do that using the below formula:

=LEN(A2)- LEN(SUBSTITUTE(A2,"a",""))

Count number of occurrence of a specific character in Google Sheets

The above formula uses the SUBSTITUTE function to remove the lower case ‘a’ from a cell, and then the LEN function counts the total number of characters without the one that we want to count.

This value is then subtracted from the total length of the original length and that gives me the character count of the alphabet “a””.

Note that the SUBSTITUTE function is case-sensitive. If you want it to count both lower and upper case of the alphabet A, you can use the below formula:

=LEN(A2)- LEN(SUBSTITUTE(LOWER(A2),"a",""))

So these are the ways you can count the number of characters in a cell or range using the LEN Google Sheets function.

Hope you found this tutorial useful.

You may also like the following Google Sheets tutorials:

Most Popular Posts

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

2 thoughts on “Count the Number of Characters in a Cell in Google Sheets (LEN Function)”

Leave a Comment