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.

**Table of Contents**hide

## 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.

**Note:**It counts all types of values like letters, numbers, special characters, space.

## 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.

**Note:**You can also count repetitive words in the same process.

## 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.