Make Excel a Little Smarter

May 22, 2004

0 1 minute read

Excel is a very smart application, but—and it’s a very big but—there are times it acts pretty dumb. However, it’s not hard to teach it to perform some very useful functions, and that’s what this article is all about—making Excel smarter. For example, when you download data to the spreadsheet from the Web or a database, Excel often takes separate numbers—such as 10, 15 and 17—and jams them all into one cell, which then looks like this:

Rather than what you would have preferred:

Or say you want to sort a list of clients by last names and each cell contains both first and last names with the first name listed first:

But what you want is

Or maybe you have data in separate cells and you want to combine them into one cell.

SPLITTING CELLS
Problem: You have multiple names or numbers in one cell and you need to separate them into different cells.

Begin by highlighting the cell or cells you want to split. The range of cells can be any number of rows tall but no more than one column wide. Then go to the taskbar and select Data and Text to Columns to bring up the screen shown in exhibit 1, at right.

You are asked to choose between the Delimited or Fixed width option buttons—although Excel likely will suggest something for you. To understand the choices, you must understand what is meant by a delimiter. A delimiter is simply a character that identifies (delimits) the end of one number or word and the beginning of another. The character can be a comma, space or a tab. Excel is smart enough to examine your data and suggest whether you have delimited or fixed-width data.