March 8, 2021

LEN function in Google Sheet

In this lesson we will learn how to use LEN function in Google Sheet. LEN function is used when you want to get the total number of characters in a specified string.

=LEN(text)
  • text : This is the string or reference to the string

Let’s understand with a simple example. In the below table we have 2 strings in column A. We have added the below function in cell B1 to find the length of the string in cell A1. 

=LEN(A1)

In the same way the LEN function in cell B2 returns the length of the string in cell A2. In this case the string in cell A2 contains numbers and special characters just to see that the LEN function can count the characters irrespective of text or numeric digits or special characters.

In below example, your string in cell A1 contains leading/trailing/consecutive more than one spaces between words. Hence the LEN function in cell B1 returns 111 as the length of string in cell A1.

In this case you can remove the leading/trailing/consecutive more than one spaces between words by using TRIM function and then use LEN function on top of it, as we added the below function in cell C1.

=LEN(TRIM(A1))

Remember the TRIM function won’t remove the single space between words. Hence the LEN function here counts the number of characters including the single spaces between words.

Now we will see how to count the number of words in a sentence in Google Sheet.

If you want to count the number of words in a sentence in Google Sheet, please use the below formula.

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

In addition to the LEN function this formula uses 2 more functions as follows.

  • TRIM – This function is used to remove the leading, trailing and double spaces.
  • SUBSTITUTE – This function is used when you want to substitute text with a new specified text in a string.

Here is the breakdown of the formula.

  • LEN(TRIM(A1)) : Here TRIM(A1) removes all leading, trailing and double spaces in the sentence (if any) and after that the LEN(TRIM(A1)) counts the total number of characters in the sentence.
  • LEN(SUBSTITUTE(A1,” “,””)) : Here SUBSTITUTE(A1,” “,””)) replaces all blanks(“ “) with no space(“”) and then LEN(SUBSTITUTE(A1,” “,””)) counts the total number of characters in the sentence without spaces.
  • Now subtract these two to get the number of spaces, and then add 1 to it to get the total count of the words in the sentence.

You can also check here to understand how the LEN function works in Excel.

Leave a Reply

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