|Do you have a list of data that’s in a format you
Do you need to “pull out” specific pieces of a
Don’t despair! You can use simple Excel functions to
Meet RIGHT, LEFT, and MID!
My sister Sandy recently bought a gift basket business. With the purchase came inventory spreadsheets that contained some important but unfriendly data that looked like this:
The previous owner used one column to hold a 3-digit Manufacturer’s code (assigned by him), followed by a standard 5-digit product item code, followed by a 5-digit list price:
Sandy called me looking for a way to extract these unique pieces of data without having to do it all by hand—and I showed her how she could use Excel’s MID, LEFT, and RIGHT string functions to complete this task—fast!
To get ready, I set up her spreadsheet like this—a new column heading for Manufacturer ID, one for Item Number, and one for List Price:
Then, to pull out the first 3 characters for the Manufacturer ID, in cell B2 I typed the following formula:
I reminded her that I have to press RETURN to complete any formula.
The LEFT function pulls the left-most characters from a string. A2 is the cell that contains the original data, and 3 is the number of characters that I wanted the LEFT function to grab. And as you can see, it did just that and got Manufacturer #770.
Next, I used the MID function in cell C2 to extract the middle characters from the string that contain the Item Number, like this:
A2 is the cell that contains the data, 4 is the starting position for the MID function to start getting the Item Number characters, and 5 is the number of characters that I wanted the MID function to grab.
Finally, I pulled out the right-most 5 characters of the string, which represent the List Price:
A2 is the location of the data, and 5 is the number of characters that I wanted the RIGHT function to grab.
Now that the three formulas were in place, I selected the cells B2 – D2:
Then, I grabbed the Fill Handle and copied the formula results to the rest of the rows in the columns:
Super easy! Now Sandy can easily sort by any piece of data to analyze and manage her inventory.
To find more great tips about how Excel for Mac can help you find things fast, take a look at the Excel 2011 Function References.
- Tierney, Office for Mac writer