Using LEFT, MID and RIGHT for String Extraction

LEFT, MID and RIGTH functions are ideal tools to extract parts of text. LEFT function is used to extract the left-most characters, MID function is used to extract selected characters within text, and RIGHT function is used to extract right-most characters of a text in Excel.

Let’s demonstrate how these functions work in the data set called Matric Numbers below:
ext1
Our intention is to extract parts of those text in Matric Number column to their respective columns which includes Entry Year, Departmental ID, and Student Number column with help of LEFT, MID, and RIGHT function respectively. This is simply explain in the short video below.

LEFT function is used to extract the leftmost characters in Excel.
Syntax: LEFT (text, [num_chars])

  • Text: the text to extract from. In this case the text is in cell A8.
  • Num_chars: Number of character to extract from left. Here we need the first two characters.
    The formula in cell B8 will be =LEFT(A8,2)
    ext2

MID function is used to extract selected characters within text in Excel.
Syntax:
MID (text, start_num, num_chars)

  • Text: the text to extract from. In this case the text is in cell A8.
  • Start_num: number to start the extraction. In this case is 6.
  • Num_chars: Number of character to extract. Here we need the next two characters. The formula in cell C8 will be
    =MID(A8,6,2)
    ext3

RIGHT function is used to extract rightmost characters of a text in Excel
Syntax:
RIGHT (text, [num_chars])

  • Text: the text to extract from. In this case the text is in cell A8
  • Num_chars: Number of characters to extract from right. Here we need the three last characters.

The formula in cell D8 will be
=RIGHT(A8,3)
ext4
That is how to extracts part of text in Excel.

Use Extracting functions with SEARCH
SEARCH function can be combine sometimes to handle advance extractions where there is no uniform number of characters to extract from text.

For instance, to extract staff name in the below data set may not be easy as we think. But with SEARCH function it becomes simpler.
ext5
What we need to do here is to use SEARCH function with this formula =SEARCH(“-”, A26) to find the location of the seperator “-“, which will give us the location number 5 then subtract 1 from it to determine the length of the Staff Name.
=SEARCH(“-”, A26)-1
we can now deploy the LEFT function to extract the leftmost with the length determined by SEARCH function above. The formula in cell B26 will be this
=LEFT(A26, SEARCH (“-”, A26)-1)
ext6
Copy the formula down to fill the remaining cells in column B.
Repeat the same process for MID function but add 1 to search function since the mid extraction will start immediately after the first hyphen (-) then specified the length of the mid extraction which is 4 in this case.

The formula in cell C26 will be this:
=MID (A26, SEARCH(“-”, A26) +1,4)

ext7
Copy the formula down to fill the remaining cells in column C.
The rightmost extraction will follow the same approach will earlier deploy since the number of characters to extract is the same. Copy the formula down to fill the remaining cells in column D.
ext8

So simple!

Download the workbook here

Hope this post is helpful?

For more Insight on Microsoft Excel, kindly buy any of my books “Productivity Boosting Aspects of Microsoft Excel” and “PivotTable Recipe” on Kindle Amazon

Shares 0