Annyca Tabassum
The Google Sheets make your work easier by developing some very handy functions. The LEN function is one of them. This function will help you to count the number of characters in your dataset and can be more efficient while combine with other functions. This article will explain the Google Sheets LEN function with examples.
A Sample of Practice Spreadsheet
You can download spreadsheets from here and practice.
What Is LEN Function in Google Sheets?
The LEN function is a basic text function that returns the number of character input in it as a parameter.
Syntax
The following is the syntax for the LEN function:
=LEN(text)
Argument
The arguments of the LEN function are as follows:
| ARGUMENT | REQUIREMENT | FUNCTION |
|---|---|---|
| text | Required | The string whose length will be returned. |
Output
The function =LEN(“Mariah”) returns with the number 6. As the word contains six characters. A cell reference can also be used instead of the text.
4 Practical Examples of Using the LEN function in Google Sheets
The use of the LEN function is quite simple and straight. You can use this function in many ways by modifying it with other functions. We will show all these examples of using the LEN function. Here, we create a dataset representing Greeting Signs. We add another column to show the results.
1. Characters in a Cell Count
You can easily count a cell’s length through the LEN function. But the LEN function always counts all the characters of a certain cell. In that case, it also counts the space. So, here we show both with-space and without-space count methods.
1.1 With Space
To calculate character with space,
- First, Select cell C5 to insert the function.
- Then insert the LEN formula.
- After that add the text. Here, we add cell B5 as the text parameter.
- Finally, press ENTER and you will find the total number of characters in the selected cells.
=LEN(B5)
- Instead of cell reference, you can directly insert text to run the function. Here, we insert “Good Morning!”.
=LEN(“Good Morning”)
- Use the fill handle to input the formula to the entire column.
1.2 Without Space
If you want to count only the value, not the space then,
- First, select cell D5 and insert the LEN formula.
- Then add the SUBSTITUTE function.
- Now, select cell B5 as text_to_search parameter.
- As we search for space so input “ “ as the search_for argument.
- In the end, insert “” as replace_with parameter.
- Finally, press ENTER to run the function and you will find only the value number excluding space.
=LEN(SUBSTITUTE(B5,” “,””))
- Use the fill handle to insert the formula into the entire column.
2. Count Characters in a Range of Cells
You can use this LEN function to count characters for a range of data. In this case, you also determine characters both including and excluding space value. To show this example we marge the result cells.
2.1 Include Space
To count a range of values including space,
- First, select the cell where you want to show the result and then input the SUMPRODUCT function.
- Then insert the LEN formula.
- Now add the range you want to count. Here, we insert B5:B9 to count characters.
- To apply the function press, ENTER.
=SUMPRODUCT(LEN(B5:B9))
2.2 Do Not Include Space
If you want to see the number of values excluding space in a range of data, then you have to combine several basic functions along with the LEN function.
- In the beginning, select the cell and add the SUMPRODUCT function.
- Then insert the LEN formula.
- After that add the SUBSTITUTE function.
- Now, select range B5:B9 as text_to_search parameter.
- Insert blank space or “ “ as search_for argument.
- In the end, add “” as replace_with parameter.
- Finally, press ENTER to run the formula.
=SUMPRODUCT(LEN(SUBSTITUTE(B5:B9,” “,””)))
3. Words Count
To work with Google Sheets most often we need to count the number of words. Though the LEN function determines the length of the text, it can’t count the number of words. But luckily customizing the LEN formula with other functions you can easily count the words in your dataset.
To show the example,
- First Select cell C5 and insert the LEN function.
- Then add the TRIM function along with the LEN function.
- Now, add the B5 cell as text.
- Insert the “-” symbol and again add the LEN function.
- After that, input the SUBSTITUTE function. Where we select the B5 cell as text_to_search parameter, “ “ is search_for argument and “” is replace_with parameter.
- In the end, add the “+” symbol and then put 1.
- Finally, to run the formula press ENTER and you will find the word count in the selected cell.
=LEN(TRIM(B5))-LEN(SUBSTITUTE((B5),” “,””)) + 1
Formula Breakdown
- The TRIM function removes space before and after the sentence. So, LEN(TRIM(B5)) counts the character of the sentence in cell B5 with space between words.
- The SUBSTITUTE function finds and removes space between words in the sentence. So, LEN(SUBSTITUTE((B5),” “,””) represents the total number of characters without any space.
- LEN(TRIM(text))-LEN(SUBSTITUTE((B5),” “,””)) will determine the total space between words.
- 1 is added to count the space before the first word.
- =LEN(TRIM(B5))-LEN(SUBSTITUTE((B5),” “,””)) + 1 represents the number of words.
- You can use the fill handle to insert the formula into the entire column.
4. Counting Specific Character
Like the previous example, you can count any specific character by customizing the formula. To do so, we have to combine the LEN function with another basic function namely the SUBSTITUTE function. For example, we want to count the “o” letter in the entire dataset. So,
- Select a cell and apply the customized formula.
=LEN(B5)-LEN(SUBSTITUTE(B5,”o”,””)
Formula Breakdown
- LEN(B5) represents the total character in cell B5.
- SUBSTITUTE(B5,”o”,””) replace the “o” character with “”. So, it represents the character excluding “o”.
- LEN(B5)-LEN(SUBSTITUTE(B5,”o”,””) calculate total character minus characters excluding the “o” letter. In another sense, it presents the number of the “o” letter in the cell.
- You can use the fill handle to insert the formula into the entire column.
Things to Remember
- Be careful while applying multiple functions.
- Always use the fill handle to insert the function downward.
Conclusion
We hope that this article will help you to get a clear understanding of the Google Sheets LEN function. To explore more about Google Sheets, visit the OfficeWheel website.
