December 4, 2020

LEN function in Google Sheet

LEN function is used when you want to get the total number of characters in a specified string in both Excel & Google Sheet.

=LEN(text)

In our below example, the LEN function is counting the number of characters in cell A1.

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

In this case you can remove those by using TRIM function and then use LEN function on top of it, to count the number of characters in the string, which comes again 54 here 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 excel or 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.

Leave a Reply

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