Excel Formulas Halfwit to Hero

The Tutorials
Chapter 1 Chapter 2 Chapter 3 Chapter 4 Chapter 5 
Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10

Chapter 7

Manipulating Text

In this chapter, you put something new in your formulas: ordinary text.

Exercise #1: Name clean up

The most common reason to use Excel's text functions is to clean up information that's not organized the way you want it. In this exercise you'll start with an old list of names that needs fixing.

  1. Begin by opening the Name clean up.xlsx spreadsheet.

  2. In column A is the list of names that you start with. What format are the names in right now?

    The names in this list are in uppercase (all capitals). The last name is first, and separated from the first name by a comma.

  3. The list of names is is a fine starting point, but what you want to end up with is two columns, one for the first name (put it in column E) and one for the last name (put that in column F), with no commas and with only the first letter capitalized. This transformation is going to take a few steps, and you need to use a combination of several formulas. Move to cell B2 to write the first one.

  4. There are two operations you need to perform here: fixing the capitalization and splitting the text. You could perform these steps in any order, but in this exercise you'll fix the capitalization first. What Excel function leaves the first letter of every word capitalized but puts the rest in lowercase?

    PROPER

  5. Move to cell B2 and write a formula that takes the text from cell A2 and fixes its capitalization. You should get the result Tyree, Sam.

    This is the function you need:

    =PROPER(A2)

  6. To split the full name into first and last names, you need to find the position of the comma that divides them. Excel has a FIND function that does the job. Move to cell C2 and write a formula using FIND that gives you the position of the comma in cell B2. You'll know you've done it right if you get the answer 6, which tells you the comma is the sixth character in the name "Tyree, Sam".

    This is the formula you need:

    =FIND(",", B2)

  7. Next, let's get the first name. What text-snipping function should you use?

    Both LEFT and MID will work, but LEFT is the slightly easier choice.

  8. Skip over to cell F2 (under the "Last Name" heading). Write a formula that uses the LEFT function, the full name text (in B2), and the comma position (in cell C2) to extract just the last name. You may need a couple of tries. Remember that you can add to or subtract from the comma position to get to the right place in your text. You'll know you've got your formula right if you see Tyree appear as the result.

    Here's the final formula:

    =LEFT(B2, C2-1)

    The LEFT function starts at the beginning and gets all the letters on the left, stopping one short of the comma.

  9. Now you need to get the first name. The calculation for this is slightly more complicated, because you need to know not just the position of the comma but also the whole length of the combined last-and-first name. What Excel function lets you count the number of characters in a piece of text?

    LEN (it's short for length)

  10. Move to cell D2. Write a formula that gets the length of your text. If you've done it right, you'll get 10 as your length.

    =LEN(B2)

    This counts the number of characters in the de-capitalized text in cell B2. Or, you could count the letters in the uppercase version of the name in cell A2, which gives the same answer.

  11. Now you have all the information you need to get the first name using the RIGHT function. But how many characters should RIGHT grab? You can calculate this by taking the full length of the combined names and subtracting the comma position. This leaves you with the number of characters after the comma. Write the first name formula in cell E2. (If you don't quite get the right snippet of text, experiment by tweaking the formula until you get what you want.)

    Here's the final formula:

    =RIGHT(B2, D2-C2-1)

    Notice the second parameter takes the full length (D2), subtracts the comma position (C2), and subtracts one more character. This removes the space after the comma. If you don't take this step, you'll get an invisible extra space at the end of the first name.

  12. Now that you have the formulas that extract the names for one cell, you can create copies for the rows underneath. Select the cells from B2 to F2, and press Ctrl+C. Then, select all the remaining cells (from B3 to F31 and press Ctrl+V to paste your copies.

    To verify that everything is in the right place, check your list of names with the names in the Solution worksheet.

  13. Once you've done your conversion, you don't need the original data anymore. However, there's a problem. If you delete the original poorly formatted names, your text-manipulation functions won't work and your properly formatted names will disappear. The solution is to convert your formulas to ordinary text values. To do that, you use a special kind of copy-and-paste operation.

     

    First, select the two columns that have the cleaned up names in them. The fastest way to do this is to click the E column header and drag over the F column header to select both columns.

  14. Press Ctrl+C to copy the two columns, but don't move anywhere.

  15. Now, you can paste the formula solutions (the text names) overtop of the original formulas. Yes, this will wipe out your formulas, but you don't need them anymore now that your text conversion work is over. To do this, you need to use the Paste Values feature. Look at the Paste button in the Home►Clipboard section of the ribbon. Click the down-pointing triangle at the bottom of the button to show a list of paste options. Then click one of the icons under the "Paste Values" heading (any one will do).

    This is the Paste Values feature you're looking for:

  16. Once you've replaced the formulas with the ordinary name text, you don't need the original data. Feel free to delete the information in columns A to D, or copy your converted names to another workbook.

  17. Interested in a bonus challenge? See if you can combine the multistep logic that gets the first and last names using a single formula for each name. To try this out, go to the Solution worksheet (because you've already wiped out the formulas your wrote with the Paste Values step). Start with the formula for the last name =LEFT(B2, C2-1) and see if you can slowly add to it. For example, replace the reference C2 with the formula in C2. You'll know you've succeeded if you can delete the information you calculated in columns B, C, and D, and your formulas in columns E and F keep working.

    This is a tricky operation and it ends up with some messy formulas. Check the Bonus worksheet to see the final result.

PREFER PAPER? If you'd rather do your work offline, you can print this page. Use this button to expand all the hidden sections and then print the exercises for this chapter: