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.

Basic LEN Function Google Sheets

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:


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:

Google Sheets LEN Formula

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:

Count A Character

Split String Into Characters

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

This one outputs the characters in a column:


Split String Into A Column in Google Sheets

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


Split String 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.

2 thoughts on “The LEN Function in Google Sheets”

  1. The ‘Split String…’ section was confusing for a moment, because the ArrayFormula shown *in the formula bar in each of the last two images* is the same, due to differing cell selections at the time of the screenshots. The first doesn’t match the formula presented just above the image.

    Thanks for the otherwise enlightening post, Ben!

Leave a Reply

Your email address will not be published. Required fields are marked *