Extract sub string from text using LEFT, RIGHT or MID function

image_pdf

The LEFT function returns the specified number of characters in a text string, starting from the first or left-most character. Use this function to extract a sub-string from the left part of a text string..

Syntax:

=LEFT(text_string, char_numbers).

Here,

text_string : The original text string which is compulsory .

Char_numbers: An optional argument that specifies the number of characters to be returned from the start of the supplied text. If omitted then it takes the value 1 by default.

In this example, LEFT function is used to extract first 7 characters from the original Text. It will also include spaces.

left1

How to use MID function in excel?

The MID function returns the specified number of characters in a text string, starting from a specified position (i.e. starting from a specified character number). Use this function to extract a sub-string from any part of a text string. 

Syntax:

=MID(text_string, start_number, char_numbers) 

Here, text_string: It is the original string.

start_number: It specifies the character number from where to start extracting the string.

char_numbers: It specifies the total number of character to be extracted from the string .

Note: If a negative value is specified for char_numbers, MID will return the #VALUE! error value; if start_number is less than 1, MID will return the #VALUE! error value. All arguments are necessary to be specified when using as a worksheet function.

In this example, MID function is used to extract 2 to 6 characters from the original Text. It will also include spaces.

mid

How to use RIGHT function in excel?

The RIGHT function returns the specified number of characters in a text string, starting from the last or right-most character. Use this function to extract a sub-string from the right part of a text string. 

Syntax:

= RIGHT(text_string, char_numbers)

Here, text_string: It is the original string.

char_numbers: It specifies the number of characters to be extracted from the string. It should be zero or greater than zero and if it is greater than length of the string the it returns full. If omitted it takes the default value 1.

In this example, LEFT function is used to extract Last 5 characters from the original Text.

right

If you find this useful, you can stay connected with us on facebook to get more amazing tips and tricks of Microsoft excel.

 

 

3,741 total views, 1 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>