Using the RIGHT, LEFT and MID Functions in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Using the TRIM & CLEAN Functions to Fix Data in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:02 Extracting Data
  • 0:34 Left Function
  • 2:49 Mid Function
  • 3:54 Right Function
  • 4:44 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed
Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Have you ever needed to capture or extract just a piece of a data string in a cell? For instance, the cell data is RGT123456K9, but all you need is the middle six numbers? The RIGHT, LEFT and MID functions will help you extract just would you need.

Extracting Data

Excel is all about working more efficiently and saving time. Each time you master another component or function of the spreadsheet software, you give yourself another tool to work smarter, not harder.

This lesson will explain the use of three directional functions that are used to extract data. These little-known and seldom-used functions are powerful! The functions are right, left, and mid. Let's take a look.

Left Function

To best understand how these functions are used, let's use an example. You just received a spreadsheet from your boss. Column A is filled with data that is a combination of letters and numbers. For instance, 'LTS145123B6'. Column A has over 100 rows filled with data just like this one. Now, there is a method to the madness so let me explain.

The first 3 characters represent the customer or vendor code. The next 6 numbers represent a product code. Finally, the last 2 characters represent the type of account or pricing structure. You've been asked by your boss to separate the three pieces into their own, individual columns.

UGH! You can certainly start typing, entering in each piece or component into a different column, one at a time. However, this would make for a very long day at the office!

Another option would be to write a formula that you could copy down the row and have Excel extract what you need. For example, extract just the first 3 characters and display the data in its own column.

Example List with Vendor Code

In your case, you want the first piece or vendor code to display in column B, starting at row 3. The syntax, or structure of the formula, would look like this:

=LEFT(source_string,number_of_characters)

So how would that look using our example spreadsheet? How would you enter the formula in B3?

It's actually simple! We're going to use the use the LEFT function, ''=LEFT''. Then, in parenthesis, enter the cell reference which is ''A3'' for the data and the number of characters to extract, which is the number ''3''. We'll then finish it off with the right parenthesis. You could then copy the formula down and voila!

Okay. Now we need to look at the second piece or the product code.

Mid Function

The product code is the middle string of 6 numbers. In the example, LTS145123B6, the product code is 145123. Now, the syntax for this formulas is =MID(source_string,start_position,length). How would this look in our example? ''=MID(A3,4,6)''

Example Product Code List

In our example, the formula would be entered in C3. The formula tells Excel to extract the sequence in A3, starting at the 4th character and extracting the next 6 characters. This is our product code. Now, the last piece. We need to extract the last 2 characters, the account type or pricing structure.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support