Use Expert Techniques To Transform Text - Part 2 of 2

By David H. Ringstrom, CPA

Last month I discussed how the Text to Columns feature in Microsoft Office Excel enables you to convert a single column of text into multiple columns. I also showed how you can use this feature to convert dates like 20080701 into Excel’s standard format 07/01/08 — as well as techniques for removing duplicates. This month I’ll demonstrate how to combine text, additional ways to break it apart, and a breezy way to convert the case of text in a spreadsheet, such as from lower case to upper case.

Combining text, a.k.a. Concatenation

Concatenation is a geeky way to describe combining two or more pieces of text together, such as a first name and a last name from separate columns, perhaps along with a title:

1. Copy this sample data into cell A1 of a blank worksheet:

First	Last
George	Washington
John	Adams
Thomas	Jefferson
James	Monroe
John	Quincy
Andrew	Jackson
Martin	Van Buren
William	Harrison
John	Tyler
James	Polk

2. Excel offers two ways to concatenate text. The first is involves the CONCATENATE function, which takes this form:

=CONCATENATE(text1,text2,…)

In this case the text arguments can either be references to other cells, or text surrounded by quotes. Enter this formula into cell C2:

=CONCATENATE(“The Honorable ”,A1,” “,B1)

3. As shown in Figure 1, the formula returns this:

The Honorable George Washington

Figure 1: The CONCATENATE function joins disparate pieces of text into a single string.

Notice that The Honorable —with a space at the end — is enclosed in quotes, as is the space that appears between the first and last name. Any non-numeric characters that don’t appear in a worksheet cell must be enclosed in this fashion.

4. CONCATENATE is simple enough to use, but you can replace the current formula in cell C2 with this instead:

=”The Honorable “&A1&” “&B1

As you can see, the ampersand enables you to combine text — sometimes referred to as strings —together just the same as the CONCATENATE function. Even better, you don’t have to remember how to spell concatenate!

5. Copy the formula in cell C2 down through cell C11 — a simple way is to double-click the fill handle in cell C2, as shown in Figure 2. The fill handle is the little notch in the bottom right-hand corner of whichever cell you currently have selected.

Figure 2: Double-click the Fill Handle to copy the formula from cell C2 down through C11.

6. Press Ctrl+C to copy the formulas to the clipboard — cells C2 through C11 should already be selected since you just copied the formula down.
7. Right-click on cell C2 and choose Paste Special.
8. Double-click on Values within the Paste Special dialog box to convert the formulas to static text. You can also click once on Values, and then click OK, but double-clicking on values saves a step.

Once you’ve converted the list to values, you’re free to delete the supporting columns, in this case columns A and B, if you no longer need that date. Or you can copy the results in column C to the desired location. Regardless, concatenation is a fast and easy way to transform text without having to retype it.

I think you’ll find many other uses for concatenation. For instance, you might need to add a prefix or suffix to a part number or account number. Or you may choose to join text and numbers together.

Concatenating Numbers

Combining text together is very simple, but mixing in dates or numbers might give you a headache. For instance, let’s say that you want this title to appear at the top of your spreadsheet:

Report Date: 05/01/08

You could enter Report Date: in one cell and 05/01/08 in a second cell, but your boss wants this information in a single cell that can be merged and centered. Many Excel users know the =NOW() function returns today’s date, so try your hand at concatenation and enter this formula:

=”Report Date: ”&NOW()

Assuming that today’s date is May 1, 2008, you may be surprised to see that Excel returns this instead:

Report Date:  39569

The number 39569 appears instead of 05/01/08 because Excel keeps track of dates as the number of days since December 31, 1899 — May 1, 2008 is 39,569 later than that starting point. Fortunately the TEXT function in Excel can transform this serial number into a meaningful date. The TEXT function takes this form:

=TEXT(value,format_text)

In this case, the NOW() function will provide the value, while we’ll use “mm/dd/yy” as the format_text will be “mm/dd/yy”.  Remember that format_text must always be enclosed in quotes.

Cheat sheet: You can easily uncover additional number formats to use with the TEXT function. Press Ctrl-1, and then choose Custom from the Number tab, and then use the list on the right as your guide.

Our completed formula will take this form:

=”Report Date: “&TEXT(NOW(),”mm/dd/yy”)

The results are shown in Figure 3.

Figure 3: Concatenation enables you to center a self-changing date across the top of your report.

Extracting Text

As you can see, concatenation is an easy way to combine text together, but you may also need to break text apart. As I discussed in Part 1 of this series, Excel’s Text To Columns feature usually makes quick work of this. However, you may encounter instances where your data doesn’t lend itself to easy parsing via Text to Columns. In such situations, you’ll likely want to avail yourself of one of these three functions:

• LEFT - This function returns a specified number of characters, starting from the left-hand side of a specified string. The function takes this form:

=LEFT(text,[num_chars])

In this case, text is either a cell reference or text contained within double-quotes, and num_chars an optional argument whereby you specify the number of characters to return. If you omit the number of characters, then LEFT returns the first character of the text.

• MID - This function returns a specified number of characters from the middle of a string, starting at whatever position you specify. The function has three required arguments, and takes this form:

=MID(text,start_num,num_chars)

As with the LEFT function, text is either a cell reference or text contained within double-quotes. Start_num is the position where you want to start, and num_chars is the number of characters that you wish to extract.

• RIGHT – This function works just like the LEFT function, but extracts text from the right-hand side of the string:

=RIGHT(text,[num_chars])

As with LEFT, omitting the num_chars argument instructs Excel to only return the last character from the string.

A Real World Example

The LEFT, MID, and RIGHT functions are easy to use when you know the number of characters or the starting point that you want to work with, but sometimes you need a more sophisticated approach. That’s where the LEN and FIND functions come in. For instance, a client recently wanted me to separate data like this into three columns:

38603 Snow Lake Shores 662

38606 Batesville 662

38606 Curtis Station 662

38606 Locke Station 662

38606 Terza 662

38609 Belen 662

38610 Blue Mount 662

38610 Blue Mountain 662

38610 Bluff 662

38610 Cotton Plant 662

 

I couldn’t use Text to Columns because some of the cities are comprised of more than one word. However, if you copy the sample data above to a cell A1 of blank worksheet, you can recreate my work:

1. Enter this formula in cell B1:

=LEFT(A1,5)

This extracts the first 5 characters starting from the left, which is this case is a ZIP code.

2. Enter this formula in cell C1:

=MID(A1,7,LEN(A1)-10)

This extracts the city name from each line, which always starts at position 7. However, the city names vary in length, so I used the LEN function to determine the length of the cell contents, and then subtracted 10 (5 characters for the zip code, 3 for the region, plus two spaces).

3. Enter this formula in cell D1:

 =RIGHT(A1,3)

This extracts the area code from the end of the string.

4. Select cells B1 through D1, and then double-click the Fill Handle in cell D1 to copy these formulas down through row 10. At this point your spreadsheet should look like Figure 4

Figure 4: You can use simple formulas to break text into multiple columns.

Change Case

As shown in Figure 5, Excel offers three different worksheet functions that enable you to change the case of your text:

• UPPER – This function converts text to ALL UPPER-CASE.
• LOWER – This converts text to all lower-case.
• PROPER – This function capitalizes the first letter of every word, with some minor side effects that I’ll show you how to overcome.

Figure 5: Simple functions can convert text to the desired format without retyping.

Once you’ve copied the formula down, copy the data and then choose Edit, Paste Special to convert it to regular text. Be sure to watch out for these caveats:

• Any characters after an apostrophe will be capitalized. This works well when you want to convert O’DONNELL to O’Donnell, but doesn’t work as well when BOB’S TV SERVICE gets converted to Bob’S Tv Service.
• You’ll have to watch out for names that begin with Mc or Mac, like McDonald or MacArthur, as these will appear as Mcdonald and Macarthur.

The views and opinions expressed in this column are those of the author and do not necessarily reflect the opinions of Microsoft.