Welcome to the 3rd part of our series on Excel tips! As proposed from our previous posts on Excel Shortcuts and Formulas, we want to help you become proficient in Excel quickly by cutting through the clutter and drill down on the most important techniques.
Introducing Text Manipulation:
In an entry level job, there are many cases where you need to analyze a large set of unstructured data you copied from a website. Often, it requires much work to decipher when you paste them into Excel. This is where text manipulation formulas can help tremendously.
Suppose you are creating a spreadsheet for your school library to keep track of which student borrowed a book. You copied a list of students’ names and their student ids from the school’s website. The data looks like this after you paste into excel:
Let’s say your goal is to have the first column containing the student ids, and the second column containing the first name only.
If there are thousands of students, it would be too time consuming for you to clean up each cell individually. Luckily, this will be a piece of cake using Excel:
1. First, you extract the student id. Let’s suppose that all id numbers have 7 digits. In this case, you can simply use:
This formula will extract the first 7 characters starting from the left. Add this to your second column as shown below.
2. Now that you have the id isolated, you will be extracting the student’s first name using this formula:
Mid(cell, starting position, number of characters)
This formula will extract, within the specified cell, a number of characters counting from the starting position. Since all student ids have 7 digits, you know that every student’s first name starts at position 8.
3. But wait…if each student’s name has a different number of characters, how would you know the amount of characters to extract? By looking at the pattern, you can see that the first space in the cell always separates the first and last name of the student. Therefore, you can find the position of the first space using:
Find(“ ”, A1, 1)
This formula will look for the first space character, within cell A1, starting from the beginning position, which is 1. If a match is found, the formula returns the position number. Otherwise, it returns “false”. Here, the result is 13.
4. If we subtract the 13 by 8 (position of the first name), we get 5, the number of characters in between the two position! In other words, to get the “number of characters” for the formula in step #2, we do Find(“ ”, A1, 1) – 8.
5. To complete the formula from step #2, we plug in our components:
Mid(A1, 8, Find(“ ”, A1, 1) – 8)
Notice that it is possible to put a formula inside another formula. This is one of the areas where Excel becomes truly powerful, where you can have almost endless combinations to do just what you need.
6. Once you have the formulas working on the first row, select them (shift + left arrow). Then, simply drag the black button on the bottom right downward to extract data from each row!
7. To keep your spreadsheet clean, you may like to delete the first column (if you don’t want to keep the raw data).
Since the formulas are still referencing the raw data, however, deleting the data will cause problems. So you would have to disconnect the formulas from the data, while keeping the results you generated.
To do this, simply select the formulas, click “Copy”, go to the “Home” ribbon, select “Paste”, and in the drop down menu select “Paste Special”.
Alternatively, you can use the keyboard shortcut we wrote about before: Ctrl+c, Alt -> e -> s -> v, Enter
Once this is complete, the formulas transform into pure values. Feel free to delete the first column.
Using text manipulation in this fashion, you’ve just got a taste of Excel’s power in automating a task. But it doesn’t stop here. Excel offers an incredibly powerful feature called Macros, and we’d love to cover this topic over the next few weeks.
As usual, Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, offers videos showing many tricks on text manipulation.
Follow us on Twitter @smarterer to stay tuned for our Part #4, Save a Life-time with Keystroke Macros.