Excel 2007: Use Expert Techniques To Transform Text - Part I

By David H. Ringstrom, CPA

Data arrives on your desk from many sources, often in inconvenient formats. For instance, fields such as city, state, and ZIP code may reside all in a single column. Or dates may appear as 20081231 instead of 12/31/2008. In other cases, you may want to delete duplicates from your list.

Many users resort to retyping lists to get the desired format, but in this article I’ll show you some expert tricks that can transform text-based lists into any format you wish with just a couple of mouse clicks. Most of these techniques work in all versions of Excel, but I’ll explain any features that are unique to Excel 2007, as well as provide alternatives for earlier versions of Excel.

Text To Columns

The Text To Columns feature enables you to quickly separate text into multiple columns:

1.  Copy and paste these records into cell A1 in a blank worksheet:

Atlanta, GA 30312

Boston, MA 02108

Chicago, IL 60606

New York, NY 10022

San Francisco, CA 94133

Remove Indent:If the example data appears in the middle of a cell when you paste it into your worksheet, press Ctrl-Alt-Shift-Tab several times to remove the indent. Alternatively, you can press Ctrl-Alt-Tab to add an indent to data within a cell. You can also press Ctrl-1 and set the Indent to zero on the Alignment tab.

2.Launch the Text to Columns Wizard:

a.In Excel 2007, choose Text To Columns in the Data Tools section of the Data tab of the ribbon.

b.In Excel 2003 and earlier, choose Data, and then Text to Columns.

3.The remaining steps are the same in all versions of Excel. As shown in Figure 1, the first step of the Text to Columns Wizard asks you to choose from two formats:

a.Delimited: You choose Delimited when you have a separator of any sort between your fields of data. Our example data has a comma between the city and state. In addition, the space between the state and the ZIP code also serves as a separator.

b.Fixed Width: Fixed width applies when your data lines up in tidy columns, say 15 characters for the city, 2 for the state, and 5 for the ZIP code. Fixed width allows you to place column breaks in specific positions.

Choose Delimited and then click Next.

Figure 1: Use the first step of the Text to Columns Wizard to indicate if your data has a separator between the fields.

4.Since you chose Delimited, the second step of the Text to Columns wizard allows you to specify the separator between the fields, as shown in Figure 2. You can choose one or more of these delimiters:

·Tab – Tab characters are non-printing characters that are sometimes represented as a square box.

·Semi-colon – Choose this when your data has a semi-colon between each field.

·Comma – Choose this when your data has a comma between each field.

·Space – Choose this when your data has a space between each field, such as first name- last name, or between state and ZIP code.

·Other – You can specify a single character of your choice in this field. Common uses include a dash (-), such as separating account numbers like 123-4567890. However, if you have a list of company names and ticker symbols, such as Microsoft (MSFT), then you could use the open parenthesis — ( — to separate the ticker symbol from the company name.

In this case, choose Comma, and then click Next.

Figure 2: Use the second step of the Text to Columns wizard allows you to specify the separator between the fields.

Separators: You can clear the check box for Tab or leave it — since this data doesn’t contain any tab characters, leaving it checked won’t have any bearing on the data. Notice that the Data Preview window shows how your data will be broken into columns as you specify a separator.

 Two Rounds Required: Ideally we’d be able to go ahead and click Space to separate the State and Zip code into two columns, but some of our cities contain a space, such as New York and San Francisco. In this instance specifying both Comma and Space would jumble our data. Instead we’ll separate the cities into one column, and state and ZIP code into a second column. We’ll then use Text to Columns again to separate state and ZIP code into individual columns.

5. Figure 3 shows the third step of the Text to Columns Wizard, where you use the Column Data Format section to instruct Excel to format or eliminate any of the columns. To do so, select a column from the Data Preview window, and then make one of these choices:

Figure 3: Use Text to preserve leading zeros, Date to convert dates to Excel’s format, or Do Not Import when you want to discard a column.

·General – Excel applies this format by default, which causes Excel to make its best guess at how the data should be treated. Fields that look like numbers are converted to numbers,  and those identified as dates in mm/dd/yy format are converted to dates.

·Text – Use this choice when you don’t want Excel to transform your data. For instance, Excel discards leading zeros when it converts text to numbers. Set a column to Text if you don’t want Excel to automatically make such changes to your data.

·Date – This choice enables you to convert dates that Excel doesn’t automatically recognize into dates in mm/dd/yy format. You choose from any of these formats:

oMDY – Month Day Year, such as 123108 or 12312008

oDMY - Day Month Year, such as 311208 or 31122008

oYMD – Year Month Day, such as 081231 or 20081231

oMYD –Month Year Day, such as 120831 or 12200831

oDYM – Day Year Month, such as 310812 or 31200812

oYDM – Year Day Month, such as 083112 or 20083112

In each of these examples, Excel would convert the dates shown to 12/31/2008.

· Do not import (skip) – This choice allows you to discard unneeded columns. For instance, if you only wanted a list of cities in this case, you could tell Excel not to import the State/ZIP field.

Since both of our fields are text based, the default choice of General works, so click Finish to complete the task.

6.At this point your data should appear as two columns in your spreadsheet, as shown in Figure 4.

Figure 4: The Text to Columns Wizard separated City and State/ZIP code into two columns.

7.Select cells B1 through B5, and then start the Text to Columns wizard again. Choose Fixed Width, and then click Next.

8.As shown in Figure 5, the Text to Columns Wizard proposes where the column breaks should be. If you don’t like a proposed column break, simply click to remove it from the Data Preview window. To add additional column breaks, click the desired position in the Data Preview window. In this case, you shouldn’t need to adjust the column breaks, so click Next to continue through the wizard.

Figure 5: Excel proposes where column breaks should appear within Fixed Width Data.

9.Since one of our ZIP codes contains a leading zero, choose click on the ZIP Code column and set the Column format to Text, and then click Finish. At this point you should have three columns of data.

ZIP Code alternative: Excel offers a ZIP code number format that can restore leading zeros — select your ZIP codes, and then press F1. Choose Special, and then Zip Code from the Number tab.

Remove Duplicates

Excel 2007 makes it extremely simple to remove duplicates from a list:

1. Copy and paste this data into a blank worksheet:

Bananas

Apples

Oranges

Bananas

Bananas

Apples

Oranges

Kiwi

2. Click on the first cell in your list, and then choose Remove Duplicates from the Data Tools section of the Data ribbon, as shown in Figure 6.

No selection required:It’s not necessary to highlight your list first Simply click on any item within the list and then choose Remove Duplicates.

Figure 6: The Remove Duplicates command eliminates duplicates from a list with two mouse clicks.

3. As shown in Figure 6, Excel automatically removes the duplicates from the list.

Earlier versions of Excel require a little more effort:

1. Copy and paste the sample data into a blank worksheet.
2. Click on the first item in your list, and then choose Data, Filter, and then Advanced Filter.
3. Click OK if a warning prompt appears regarding headings. This prompt sometimes appears even if your list has a heading in the first row.
4. Choose Copy to Another Location, and enter a cell address in the Copy To field. For instance, if your list is in cells A1 through A8, enter C1 in the Copy To field.
5. Choose Unique Records Only, and then click OK.

 

Part II of this series will describe other ways to transform your data, such as changing the case, removing extraneous words, and joining pieces of text together.

The ability to export and import form layouts enables you to create customized screens for your clients, or to ensure that related Office Accounting entities share the same look and feel.

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