Extracting Characters from Text Using Text Formulas

 

To extract the characters, words, first, middle and last name from a cell, we use the formulas “LEFT”, “RIGHT”, “MID”, “LEN”, and “SEARCH” in Microsoft Excel.

LEFT: Returns the first character(s) in a text string based on the number of characters specified.

Syntax of “LEFT” function:            =LEFT (text,[num_chars])

For Example:We have name in a cell and we want to extract the first word from the cell.

 

image 1

 

To extract the first name from the cell, enter the formula in cell:-

=LEFT (A2, 7), function will return“Broncho”

 

image 2

 

RIGHT: Returns the last character(s) in a text string based on the number of characters specified.

Syntax of “RIGHT” function:        =RIGHT (text, [num_chars])

For Example:We have name in a cell and we want to extract the last word from the cell.

 

image 3

 

To extract the last name from the cell, enter the formula in cell:-

=RIGHT(A2,8), andfunction will return “Anderson”

 

image 4

 

MID: Returns a specific number of character(s) from a text string, starting at the position specified based on the number of characters specified.

Syntax of “MID” function:=MID (text,start_num,num_chars)

For Example:We have name in a cell and we want to extract the middle word from the cell.

 

image 5

 

To extract the middle name from the cell, enter the formula in cell:-

=MID(A2,9,5), function will return “Billy”

 

image 6

 

 

LEN: Returns the number of characters in a text string.

Syntax of “LEN” function:             =LEN (text)

For Example: We have name in a cell and we want to calculate the length of text.

 

image 7

 

To calculate the length of text, enter the formula:-

=LEN(A2), function will return22

 

image 8

SEARCH:The SEARCH function returns the starting position of a text string which it locates from within the text string.

Syntax of “SEARCH” function:      =SEARCH (find_text,within_text,[start_num])

For Example:We have name in a cell and we want to search “Billy” within text.

 

image 9

 

To find out the Billy in the cell, enter the formula in cell:-

=SEARCH(“Billy”,A2,1), function will return 9

 

image 10

 

These are the formulas which we use to extract characters from text, using text formulas in Microsoft Excel.

image 4

 



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>