Tips for Excel, Word, PowerPoint and Other Applications
Text Parsing in Excel: LEFT, MID, RIGHT
Why It Matters To You
There are times when you might want to extract specific parts of a cell's contents. For example, a fleet vendor may track your company's leased vehicles by and ID number consisting of business division, PS code, and vehicle ID. We may need to extract the PS code so that we can report to specific departments about their leased vehicles.
Being proficient at extracting data speeds up our analysis, eliminates a lot of manual entry, and makes us more comfortable with data is that isn't 'perfect'.
How To ...
Excel has 3 functions, LEFT, RIGHT, and MID, which surprisingly, allows us to extract data from the left, right, or center of a cell. Here is the structure of the 3 functions.
For LEFT and RIGHT, all you have to do is specify a cell and tell Excel how many characters, starting from the left or right, you want to extract. MID is a little more complicated, but all you have to do is tell Excel at which character to start, then how many characters to extract, starting from the start character. Still with me? This is about as hard as it gets.
LEFT, RIGHT and MID in Action
Let's try an example. Here is the typical vehicle code that one fleet vendor I've worked with uses to identify fleet vehicles.
10-506005XXXX-010 (imagine this is in cell B3)
The Vehicle ID# consists of the major business unit, the department code, XXXX then a code to indicate the vehicle #. All of these data elements could be interesting at one point or another. Here's how the 3 functions work:
| Formula | Result | What the heck just happened? |
|---|---|---|
| =LEFT(B3,2) | 10 | Tells Excel, extract the 2 leftmost characters |
| =RIGHT(B3,3) | 010 | Tells Excel, extract the 3 rightmost characters |
| =MID(B3,4,6) | 506005 | Tells Excel, start with the 4th character, then extract the next 6 characters |
As you see, with just a little bit of work, I can extract a great deal of information out of this Vehicle ID# and save myself hours of manual transcription. In the case of my data set, imagine that I have 500 fleet vehicles and I'm trying to prepare a report of vehicles by department code, the MID formula, plus a 15-second pivot table generates the list and I can go home early.
Notes
| Last updated | 9/3/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |