Microsoft Excel 2007 tutorial: combine or split contents of cells


Combine or split the contents of cells

XXVI. Combine or split the contents of cells

You can combine the contents of multiple cells into one cell, and you  can also split the contents of a cell into separate columns.

XXVI.1. Combine the contents of cells into one cell

In combining the contents of cells into one cell, you can use a formula with ampersand operator (&), and CONCATENATE function.

To use the formula with the ampersand operator (&), do the following:

- Click the cell that you want to display the combining contents of multiple cells

- Type the equal sign (=)

- Select the first cell of the cells that you want to combine

- Type ampersand operator (&)

- Select the second cell of the cells in which you want to combine. If you have other cells, type the ampersand operator (&) and then select the next cell. Do like this until the last cell (e.g.  A1&B1&C1...).

- Press Enter to see the result

The result of the formula above does not include a single space between each word. To add a space, you need to insert it between the ampersand operators as &" "& (with a space between the quotation marks). In case, you want to add comma between each word, type &", "& (with a comma follow by a space between the quotation marks) between the selected cells.

For example, you want to combine the first name cells of the First Name column with the last name cells of the Last Name column to display them as the full name with a single space between first and last name, in the Full Name column of the table below.

Excel 2007 Name List

To do this task, follow the procedure:

1. Click the first cell of the Full Name column (C3)

2. Type =

3. Select the first cell of the First Name column (A3)

4. Type &" "&

5. Select the first cell of the Last Name column (B3)

6. Press Enter

In sum, from step one to five, you can type in C3=A3&" "&B3

For the row four and row five, you no need to type this formula again. You just click the C3 cell, then move the mouse point to the lower right corner of the C3 cell, and then drag down to the row five.

Excel 2007 the lower right corner of cell

You can also use the CONCATENATE function as the following:

- Click the cell that you want to combine the contents of multiple cells

- Type the equal sing (=), and then type the function. When you type the con word, Excel lists the list of functions that start with con word, so you can select CONCATENATE function from this list.

- Select cell1, and then type comma, select cell2, and then type comma, do like this till the last cell.

- Press Enter to see the result.

If you want to add a single space between each word, insert it between the commas of the selected cells. For example, =CONCATENATE(A1," ", B1).  If you want to add a comma, type &", "& (with a comma follow by a space between the quotation marks) instead of &" "&.

For instance, use the example above to practice with the CONCATENATE function.

1. Click the first cell of the Full Name column (C3)

2. Type =CONCATENATE(

3. Select the first cell of the First Name column (A3) follow by comma.

4. Enter " " (with a space between the quotation marks) follow by comma, and then select the first cell of the Last Name column (B3) follow by closed bracket ).

5. Press Enter

Totally, from step one to five, you can type in C3=CONCATENATE(A3," ",B3)

XXVI.2. Split the contents of one cell into separate columns

You can split the text of a cell into other columns by using Text to Columns feature on the Data tab.

For example, you enter the following text: StudentID, Student Name, Sex, and Date of Birth in one cell and want to separate them into separate columns.

Excel 2007 cell contents

To do this task, do the following:

1. Select the cell that contains the text values

2. On the Data tab, in the Data Tools group, click Text to Columns.

Excel 2007 Text to Columns feature

The Convert Text to Columns Wizard- Step 1 of 3 displays.

Excel 2007 convert text to column step 1 dialog box

This dialog box determines the text which you type in the cell. It is Fixed Width or Delimited. For the description of  the Fixed Width and Delimited types, read it on this dialog box. You can also see your selected text in the Preview of selected data list.

3. Click Next button

The Convert Text to Columns Wizard- Step 2 of 3 displays.

Excel 2007 convert text to column step 2 dialog box

This dialog box allows you to set field width. The arrow lines shows a column break. To the use the arrow lines, do the following:

- To create a break line, click the desired position.

- To delete a break line, double-click on the line.

- To move a break line, click and drag it.

4. Remove the break lines between Student and Name, and Date and of , then click the break line on the left of Birth, and then drag it to the right of Birth.

Excel 2007 set field width

Lastly, click Next button.

The Wizard dialog box of the last step appears:

Excel 2007 convert text to column step 3 dialog box

This dialog box allows you to set the Data Format for each column. To set the Data Format, click the column, and then click any data format in the Column data format group. For other columns that you want to set the Data Format also do this way.

5. Click Finish button. You will see the contents of one cell split into other columns.

Note: If your data is different from the example data, follow the Convert Text to Column Wizard dialog box that will introduce you from step to step.


Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.