#128 Excel: Text To Columns

Excel’s Text to Column function allows you to break text contained in a single column into several columns. For example, if you have “LastName, FirstName” in a column, you can break the text into two columns—one containing LastName and the other containing FirstName.

Excel’s Text to Columns feature allows you to separate text contained in a single cell and split it into multiple columns. For example, a full name appears in one cell (e.g., Taylor, Mike). You can use Text to Columns to put the last name in one column and the first name in the next column.

This data must be separated by delimiters, such as commas or spaces. Excel allows you to separate cell data either by selecting predefined delimiters or using custom delimiters.

Another example would be something like this report generated by our new Learning Management System (KEY). One of the fields has a bunch of “junk” that I really don’t want. By splitting this one column into two I can easily remove the unwanted parts.

In this case I specified the left parentheses character “(” as the delimiter which split the contents of the cell at that point. Then I can simply delete the new Column D and go on my merry way.

WARNING:

Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one or more blank columns to the right of the column you are separating.

How It Works:

1. Select the data to be separated.

2. From the Data menu select Text to Columns…

3. In the resulting wizard select the Delimited option and click the Next button.

4. In the Delimiters section indicate what you’re using to split the data, then click the Next button.

5. Optional: The final step of the wizard lets you set the format of these new columns and/or indicate a different location for these columns.

6. Click OK to replace existing columns.

Considerations:

This might take a little practice to get it exactly the way you want but if you’re interested here are some of the things you should consider along with some other relevant details: http://www.uwec.edu/Help/Excel07/ws-separate.htm

See It In Action:

…and leave me a comment while you’re there!


http://screencast.com/t/uBKTHVTIreF

Special Note to Spreadsheet Monkeys:

I realize you can also do this with a variety of different Excel formulas or VB code but for us mere mortals this way might be a bit easier. 8-D

What do you think? Useful? Have a better way?

Advertisements

One thought on “#128 Excel: Text To Columns

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s