LEN Function in Google Sheets and Practical Use of It

LEN Function in Google Sheets

Honestly, I am very rarely using Google Sheets LEN function. Still at one point, I find it very tricky. I normally use LEN function in Google Sheets to control the ARRAYFORMULA expansion to adjoining cells. I will come to that later.

How to Use LEN Function in Google Sheets

LEN function returns the length of a string. That is the whole thing it does.

Syntax: LEN(text)


LEN Function example in Google Sheets

See the third and fourth formulas. From that, you can clearly understand that LEN formula counts space also. Now one Practical Use of Google Sheets LEN Formula.

As mentioned above, I am using LEN with Google Sheets ARRAYFORMULA to limit the expansion of result to certain cells.

Practical Use of LEN Function in Google Sheets


LEN in Google Sheets with ARRAY and IF

I have certain data under column A & B. In column C, I’ve joined the text using “&” operator together with ARRAYFORMULA. Though I have applied the formula in cell C13 only, it automatically expands to below cells due to the ARRAYFORMULA. As a result, the expected outcome of the formula is not proper. See the marked content on the above image. Actually it’s not required there. See the formula in use below.

=ArrayFormula(A13:A& ” ” &B13:B)

We can remove the marked contents using a logic. In this case, we can limit the formula to only return the result, if there is any content in column B. Below is the modified formula.

=ArrayFormula(if(LEN(B13:B),A13:A& ” ” &B13:B,””))

See the highlighted part. We used Google Sheets IF and LEN together. What this combination does?

Here the LEN formula returns the length of String in Column B. The IF logical function only join the texts if LEN is not equal to 0.

The result will be as follows.

Result of LEN with IF

Can’t we get the above result without using LEN? Yes, you can. See an alternative formula.

=ArrayFormula(if(B13:B<>””,A13:A& ” ” &B13:B,””))

Actually LEN formula replaces “B13:B<>””” part of the formula with “LEN(B13:B)“. Hope you enjoyed this tutorial. Use LEN in similar cases.


Please enter your comment!
Please enter your name here