Do you bristle when you see someone wasting precious time? I do, and I can’t help but stick my nose in and suggest a better way. That’s what happened recently when I noticed one of my coworkers using Excel and keying something from scratch that could have been generated with a simple formula. Here’s the scoop.
If the text is embedded in a cell, you can extract it
My coworker received a worksheet from a vendor that contained a column of entries that looked like the ones shown in Figure A.
|We’ll use functions to extract certain portions of the entries in column A.|
Those strings contained three distinct parts:
- The first three characters (the K-numbers) represent a product code.
- The second two digits (the B-numbers) represent a price code.
- The final three digits represent a customer code.
My coworker wanted to separate out those three pieces into different columns, and she was retyping them from scratch! That approach is so wasteful and inefficient it makes my skin crawl. Fortunately, I got to be the hero by showing her how to use Excel’s string functions to extract the codes automatically.
Fun with string functions
All of you veteran spreadsheet users know this drill by heart. Here’s how it works.
Grabbing the first three characters. To extract the first three characters of the text entries, you enter the Left function like this:
In this case, we entered into cell B2 the function =Left(A2,3) and then copied that formula to cells B3:B8. Figure B shows the results.
|The Left function eliminates the need to re-key the first three letters from the entries in column A.|
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:
Since we know that the string we want to extract always starts in position 4, we entered into cell C2 the function =Mid(A2,4,2) and then copied that formula to cells C3:C8. Figure C shows the results.
|The Mid function lets you pull a string out of the middle.|
Extracting the last three characters of a string. In order to extract the last three characters of a string, you use the Right function in the form:
In our example, we entered in cell D2 the function =Right(A2,3) and copied it into cells D3:D8. As Figure D shows, that function returns the three rightmost characters in the source string.
|The Right function makes it easy to copy a set of characters from the right side of a string.|
Once you’ve extracted the strings, then what?
After you’ve used the string functions to parse the source string into substrings, you’re free to sort or subtotal your data on any of the columns that contain those substrings. It only takes a minute or two to compose the function call and copy it to the appropriate cells. This technique comes in handy when you’re importing text files that have dumped from a mainframe database or from some other application.
Of course, we’ve just scratched the surface of what you can do with the “big three” string functions. In this simple example, our source string always contained the same number of characters.
Knowing that the middle string always started in the fourth position made it easy to use the Mid function. But what if the substring you want to extract could start anywhere within the string? In a future article, we’ll show you how to use the Find function in conjunction with the Mid function to locate and extract a string, regardless of how many characters the source string contains.
To comment on this article or to share your favorite Excel tip, please post a comment below or drop us a note.