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:

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

=LEN(B2)

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.

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

Below is the formula that will do this:

=SUMPRODUCT(LEN(A2:A6))

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

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:

• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• How to Count Cells with Specific Text In Google Sheets
• How to Get the Word Count in Google Sheets
• Change Text Case Using a Formula in Google Sheets
• How to Number Rows in Google Sheets (Add Serial Numbers)