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
=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.
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:
=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
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.
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!
Good spot, Tony! Thanks for the heads up.
I’ve replaced that image to show the correct formula now.
Cheers,
Ben