Excel - Merge or separate data in columns

Separate data from one column into multiple columns

Okay, here's our challenge: The roses and their information are listed in individual cells. The user would like the details separated into columns, one for the Rose name, one for the rose color, and one for the rose variety.

Highlight the rose data in cells B4:B6

Click Data, Text to Columns to open the Text to Columns wizard.

Choose the Delimited file type and click Next.

 Select the Delimiter format, in this case Comma, and deselect any other formats that may be selected.

Check to be sure the Column data format is selected, and click Finish.

(In this case, separating the data into separate columns is done through the Text to Columns wizard, so all data function as normal cell values.  There is no need to convert the data to values as was done below when combining data from separate columns.)

In the Excel Basics textbook exercise, data is separated, values are set, and then data adjusted by way of functions/formulas to capitalize names and concatenate with further info. With the textbook situation, it is necessary to perform the Copy and Paste Special instructions shown below so that the data can stand by itself. I'm in the process today (5-6-10) of modifying this instruction sheet to show the textbook exercise in full.

Merge data from multiple columns into one column

In this case, social security numbers have been split into three parts, with each part entered into its own column, in E, F, and G.

To combine the numbers and include dashes between each section, create a formula in cell H13:

(Substitute your cell names for the ones I’ve used in these instructions.)

      =E13&"-"&F13&"-"&G13

The ampersand symbol (&) tells Excel to add on information to what has been placed into the formula before it.

The quotation marks that surround the dashes tell Excel that the character within the quotes is to be inserted at that point into the combined information.

Pull the formula down into the cells below.

 This formula won’t continue to work if you were to delete the data in Columns E, F, and G.

So next change the formula-generated values into actual values within the Column H.

1. Highlight all of the cells containing social security numbers in column H.

2. Press Ctrl+C (or Edit, Copy) to copy those cells.

3. Now, without unhighlighting the cells, immediately do an Edit, Paste Special.

4. In the Paste section of the window, click the radio button for values, and then click OK.

    5. Now you can safely delete data from columns E, F, and G.