How to remove first,last or certain characters from text using LEN function?


The LEN function in EXCEL is used to return the length of the text string.


= LEN(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")

if len

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>