↑ Return to Text functions

LEN


 

The LEN function returns the number of characters of a string in a cell.

This function is rarely used alone. You can use it in a conditional formatting or data validation.

 

A very common example use of the function LEN is how to extract the firstname of the column A. The firtname is after the comma in this case.

 

 

 

 

 

The problem, here, is to calculate the number of characters between the comma and the end of string. This value is never the same because the firstname of your contacts in obviously not the same. So we have to build a formula with the function LEN.

In the following example we want to split the last name and the first name in the column A.

In column B we used the SEARCH function to returns the position of the comma in the cell. In column C, we have the number of characters in the cell (double-click in cells to see the formulas).

How to calculate number of characters to extract?

In the cel A2l, it’s easy to see the value to find is 4. To achieve this, we must first get number of characters in the cell and subtract the value corresponding to the position of the comma. The result is shown in column D.

The result is 5 🙁
We want to find 4. The difference is due to the space after the comma. So, get the number of characters corresponding to the firstname, is returned by this formula:

= C2-B2-1

And applied in the second argument of the function RIGHT, this becomes

With as complete formula:

= RIGHT (A2, LEN (A2)-SEARCH (“”, A2) -1)

Leave a Reply

%d bloggers like this: