The LEN function in EXCEL is used to return the length of the text string.
Here , text_string: It is necessary argument and returns the length of string. Spaces are counted as characters. Null string returns 0 values by default.
Let's see an example.
For Example, "Horry Potter" text has total 12 characters including space
How to use IF + LEN function in excel?
IF+LEN function combination can be used to set criteria based on length of string.
For example, use this formula to display text in cell B1 based on the length of String in cell A1.
=IF(LEN(A1)>15, "write", "writeno")
Use RIGHT+LEN to remove certain characters from rightmost part of given String
We can use RIGHT and LEN function together when we want to remove some characters from right part of the string and we don't know the length of string or string length is variable in different cell.
For example, here we want to extract last some characters from string in Column A. Each value is with combination of Number and Text. We want to separate numbers from Text.
In this example, Numeric value (i.e. 4315) is variable but Name characters (i.e. Aagam) are constant. Use LEFT function if you want to extract Name part. (See next example)
First find out the total number of characters in cell A1, then substract the first 6 characters(including space) to get the remaining value using RIGHT function
Check the formula and execution steps to understand how it works.
Use LEFT+LEN to remove certain characters from leftmost part of given String
Same as above you can use LEFT function when you want to extract some characters from leftmost part of string.
If you find this useful, you can stay connected with us on facebook to get more amazing tips and tricks of Microsoft excel.
17,142 total views, 5 views today