fbpx

Extract values from cell using LEFT, RIGHT, MID

Yodalearning>Tutorials>Extract values from cell using LEFT, RIGHT, MID

Imagine that you wanted to create a new column on a spreadsheet. But part of the information is already in another column? For example:

You wanted to distribute the first, middle and last names from a cell into three separate cells.

Do you bristle when you see someone wasting precious time for this?

Case study 1

Think you have a column in your excel sheet and each cell’s strings contained three distinct parts. As like the above picture:

  1. The first three characters (the P-numbers) represent a product code.
  2. The second two digits (the B-numbers) represent a price code.
  3. The final three digits (the C-numbers) represent a customer code.

You wanted to separate out those three pieces into different columns.

If the text is embedded in a cell, you can extract it!

One easy way to solve this problem is by using the Left, Right, or Mid functions.

But before we learn How to Extract values from a cell using LEFT / RIGHT / MID, We must learn what are these functions.

LEFT function lets you specify the number of characters you want. Either from the beginning of another cell or from a specified string of text or numbers.

RIGHT function lets you specify the number of characters you want. Either from the end of another cell or from a specified string of text or numbers.

MID function lets you specify the number of characters you want. Either from a point, you specify in the middle of another cell or specified string of text or numbers.

Now let’s do it using the following steps!

Create a table

Step 1: Create a table same as like above picture.

Column A is showing the Whole Code from where we are going to extract.

Column B is to extract only Product Code from Code column A.

Column C is to extract only Price Code from Code column A.

Column D is to extract only Customer Code from Code column A.

Now let’s fun with string functions of cell A2.

Grabbing the first three characters Step 2: Grabbing the first three characters (Product Code).

To extract the first three characters which are product code of the text entries. Use the Left function like this in Cell B2:

 =Left(source_string,number_of_characters)

In our example:

source_string: A2

number_of_characters: 3

So, In cell B2 we can type:

=Left(A2,3)

Now press ENTER and see the Magic!
we get only the product code from cell

Step 3: Now we get only the product code from cell A2 into Cell B2 like above picture. Now copy this formula into other cells of column B using Fill Handle from selected cell B2. So, we will get same another product code from other cells of column A.

we have got all product code in Column B

Step 4: Now we have got all product code in Column B as like above picture.

: Pulling out the two characters in the middle

Step 5: Pulling out the two characters in the middle. To extract the two characters in the middle of the string, we’ll use the Mid function, which takes the form:

 =Mid(source_string,start_position,length)

In our example:

source_string: A2

start_position: 4

Length: 2

Now let’s extract Price Code from the cell A2 into C2 using this formula:

=Mid(A2,4,2)

Then press ENTER and copy the same into other cells of column C.
now we have got the Price code in column C

Step 6: So, now we have got the Price code in column C.
Extracting the last three characters of a string

Step 7: Extracting the last three characters of a string. To extract the last three characters of a string, you use the Right function in the form:

 =Right(source_string,number_of_characters)

In our example:

source_string: A2

number_of_characters: 3

Now let’s do the same in Column D to extract Customer code using below formula:

=Right(A2,3)

Press ENTER and copy the same in other cells of Column D.
How to Extract values from a cell using LEFT, RIGHT, MID

Now, we have got our final result as seen in the above picture. We have also learned How to Extract values from a cell using LEFT, RIGHT, MID.

Enjoy Excel!

Excel Dashboard
Limited Period Offer.

30-Day Money-Back Guarantee

Includes:


11 hours on-demand video
Full Lifetime Access
Success on mobile and TV
Certificate of Completion
Downloadable Case Studies
Lifetime Support