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.
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.
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.
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:
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.
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)''
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.
The right function is similar to the left function. However this time, Excel will start looking on the right side of the data string, rather than the left.
The syntax for the formula is =RIGHT(source_string,number_of_characters). So in D3, you would enter the following formula, ''=RIGHT(A3,2)''. Starting on the right side of the data string, Excel will extract 2 characters. In this case, the account type. Now, once you have your formulas entered, you can copy them down the column to populate your data.
This lesson explained how to use the left, mid, and right functions in Excel formulas. These directional functions are very helpful when you need to extract pieces of data from a string of characters in one cell. You learned that by using these functions, you can tell Excel where to look and how many characters to extract. It's really quite simple!
Before we finish up, let's do a quick review of the syntax, or formula structure, and the actual formulas we used in our example.
The first one was left:
- Now the syntax was =LEFT(source_string,number_of_characters)
- Now, the actual formula that we used ''=LEFT(A3,3)''
Then we looked at the mid:
- The syntax for the mid was =MID(source_string,start_position,length)
- Our actual formula was ''=MID(A3,4,6)''
Then, finally we looked at right function:
- The syntax was =RIGHT(source_string,number_of_characters)
- Our actual formula was ''=RIGHT(A3,2)''
There are great time-saving functions. Try them out next time you need to extract data in an Excel worksheet.
You've finished the video and now you should be able to:
- Understand how to use the three directional functions to extract data in Excel
- Describe how to use the left function
- Explain how to use the mid function
- Demonstrate how to use the right function