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.

LEN Function Syntax

`=LEN(text)`

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.

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:

`=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))`

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:

`=ArrayFormula(MID(A1,SEQUENCE(LEN(A1)),1))`

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

`=ArrayFormula(MID(A1,SEQUENCE(1,LEN(A1)),1))`

LEN Function Template

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.

2 thoughts on “The LEN Function in Google Sheets”

1. Tony says:

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!

1. Ben says:

Good spot, Tony! Thanks for the heads up.

I’ve replaced that image to show the correct formula now.

Cheers,
Ben