HomeGoogle DocsSpreadsheetLEN Function in Google Sheets (+ IF Combination)

LEN Function in Google Sheets (+ IF Combination)

Published on

The LEN function returns the length of a string, which is particularly useful in logical tests in Google Sheets. We can use this function mainly in two ways:

  1. With the IF function to evaluate whether a cell has a value or not.
  2. Within data validation to allow or reject strings of a particular length.

Before we delve into that, let’s examine the syntax of the LEN function in Google Sheets.

Syntax and Arguments

LEN(text)

The function has only one argument, which is ‘text’. This is the string input for which the length will be returned.

However, it’s worth noting that the LEN function can also return the length of dates, numbers, or timestamps in a cell. Though usually, we may not need to find the length of such values.

Examples:

=LEN("APPLE") // returns 5
=LEN(123456789) // returns 9

In the following example, I’ve mixed data types in cells A2:A9. I’ve entered the following formula in cell B2 and dragged it down.

=LEN(A2)

Please refer to the screenshot below.

Examples of LEN function in Google Sheets

Note: The LEN function will return the count of all characters in the text, even spaces and non-printing characters.

LEN Function in Google Sheets + IF Combination

Assume you have the following formula in cell B1:

=A1*100

It returns 0 when A1 is blank. In that case, you want to return blank instead of 0. You can do it mainly in two ways:

=IF(A1<>"", A1*100,)
=IF(LEN(A1), A1*100,)

In these formulas, A1<>"" and LEN(A1) are the logical expressions in the IF function.

Syntax: IF(logical_expression, value_if_true, value_if_false)

The combination of LEN and IF is widely used by Google Sheets users. So, if you see =IF(LEN(reference), read it as =IF(reference<>""

In the following example, I’ve used this combination in an array formula to return the concatenation of titles in cells A13:A and names in B13:B, returning blank if the name is blank.

=ArrayFormula(IF(LEN(B13:B), A13:A& " " &B13:B, ""))

The result will be as follows:

LEN and IF combination

Here is an alternative formula:

=ArrayFormula(IF(B13:B<>"", A13:A& " " &B13:B, ""))

LEN Function in Data Validation in Google Sheets

You can understand the importance of the LEN function in data validation from the example below.

I want to ensure that all the values in the range C1:C10 are text and 10 characters in length, as those are product IDs and we use 10-character length product IDs.

We can use the following combination of AND, LEN, and ISTEXT functions as a custom formula within data validation as follows:

  1. Select cells C1:C10.
  2. Click on Data > Data validation > Add Rule.
  3. Select “Custom formula is” under Criteria.
  4. Enter the following formula: =AND(LEN(C1)=10, ISTEXT(C1)).
  5. Click Done.

Resources

You can find several formulas in this blog that utilize the LEN function. Here are a few tutorials:

  1. Padding Values with Spaces to Make Them Equal in Length in Google Sheets
  2. Removing or Replacing the Last Character from a String in Google Sheets
  3. Formula to Reverse Text and Numbers in Google Sheets
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

8 COMMENTS

  1. =ArrayFormula(if(LEN(B13:B),A13:A& " " &B13:B,""))
    Why the len(B13:B) has no condition? I keep trying like yours, but it doesn’t work.

  2. I have three columns. A = Names, B = Date, C = Answer(Yes/No).

    Users can submit the Form each day. But can choose the Answer “Yes” only once a week.

    If by mistake, an individual has input “Yes” multiple times within a week, it should be counted just 1 (as a rule, once a week)

    I tried your various examples on this site but could not find the solution.

    Can you please help me with this problem?

    • Hi, David,

      Make column D empty. Then in cell D1, insert the below formula to return a column with week numbers.

      =ArrayFormula({"Week #";if(A2:A="",,weeknum(B2:B22,2))})

      Now empty the columns E and F. Then in E1, the Query will return the required count.

      =query(query(A1:D,"Select A,count(C) where C='Yes' group by A,D"),"Select Col1,count(Col2) group by Col1 label Count(Col2)'Count of Yes'")

      Does this solve the problem?

      For any further assistance on this matter, please share a sample sheet URL below.

  3. I want to find the number of occurrences of, say, “X” within a range of cells, where “X” may occur multiple times within any cell.

    • Hi, Richard,

      Here are two formulas that you can try.

      Range: A2:D4

      Case-sensitive:

      =LEN(regexreplace(textjoin("",true,A2:D4),"[^x]",""))

      Case-insensitive:

      =ArrayFormula(LEN(regexreplace(textjoin("",true,lower(A2:D4)),"[^x]","")))

  4. In the expanding count formula, in cells Values, P for present and A for absent values are there. How can I count P or A for each row?

LEAVE A REPLY

Please enter your comment!
Please enter your name here