Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 9

Text Manipulation

TEXT TO COLUMNS

When processing data from other sources, you may either get information where all the data is in one column or receive text files, which have the same problem. Upon opening the file, all the data is condensed in a single column. Excel has a feature that enables you to separate the data into individual columns. Look at the data on the sheet shown in Figure 9.1. The cells in column one contain all the information that should be spread out over a number of cells.

FIGURE 9.1 All the Text Is in One Column

You can see in the formula bar that all the information is the in cell A3: Alfano, Vincenzo, 19.4, 38.3. To correct this problem, you could use the Icon command in the Data menu called Text to Column to reformat your data. Use the Text to Column feature to separate the data into individual columns. See Figure 9.2.

FIGURE 9.2 The Text to Column Menu Location on the Data Ribbon

Note that the data, in the cell, is separated by commas. (Excel refers to data with a separator such as a comma as “delimited” data.) Separating the data into individual columns is done through the Text to Column menu. Select column A and activate the menu. See Figure 9.3.

FIGURE 9.3 The Text to Column Menu

The menu has two options, Delimited or Fixed width. Since ...