The LEN Function in Google Sheets

The LEN function in Google Sheets is a simple but useful function that counts the number of characters in a given string.

If you’re working with text data in spreadsheets then you’re likely to use the LEN function. It becomes particularly powerful when combined with other functions to manipulate and analyze strings.

🔗 Grab the LEN template at the bottom of this article.

LEN Function Syntax

It takes one argument:

text

This is the string for which you want to count the number of characters.

LEN Function Notes

The LEN function counts every single character, including spaces and punctuation.

Simple LEN Function Example

Suppose you have the following dataset and you want to find the length of each comment. Use the LEN function to do this:

This will give you the number of characters in each comment.

Advanced LEN Function Techniques

Where LEN really shines is in combination with other functions. It’s especially useful when you’re working with strings. Let’s see some examples:

Count A Specific Character With A LEN Formula

To count the number of times a specific character occurs in a string, use the SUBSTITUTE function with LEN.

SUBSTITUTE removes the specific character from the string and then LEN measures the length before and after removing the specific character. The difference is the number of times that specific character occurred in the original string.

Here is the formula to do this:

In the following example, this formula is used to calculate how many times “a” occurs in the text:

Split String Into Characters

To split a string into individual characters, use these formulas.

This one outputs the characters in a column:

And, with a slight modification to the amazingly powerful SEQUENCE function, this one outputs a row:

LEN Function Template

Click here to open a view-only copy >>

Feel free to make a copy: File > Make a copy…

If you can’t access the template, it might be because of your organization’s Google Workspace settings.

In this case, right-click the link to open it in an Incognito window to view it.

It’s part of the Text family of functions in Google Sheets. You can read about it in the Google Documentation.