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 *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.