Divide, Combine and Join Cell Content in Excel

How to separate/divide content of one cell into multiple cells in Excel? The easiest way is using Text to Columns tool. In Office 2003 you can access Text to Columns from Data menu. In Office 2007, Text to Column is located in Data tab. Text to column is useful when you want to convert or display file format like .csv into table or just separate/divide content of cells.

For example, we have data like this

Full Name	First Name	Last Name
Barack Obama
Michael Bolton
John Outlook
Cameron Diaz
Diego Maradona

To separate values in all columns, here are the steps:

1. Select all cells contain data you want to divide
   
2. Go to Data - Text to Columns…
   
3. Choose Delimited or Fixed Width depends on your data. In this case, we use Delimited. Next.
   
4. Check on Space and leave the others delimiters blank. If the data contains three words, it will be separated into three columns. You may choose other delimiter, also depends on your data. Next.
   
5. Choose the data format of separated data should be. Leave the destination by default if you want the new separated contents overwrite your original data or define new destination. Finish.

Here is the result:

Full Name	First Name	Last Name
Barack Obama	Barack	Obama
Michael Bolton	Michael	Bolton
John Outlook	John	Outlook
Cameron Diaz	Cameron	Diaz
Diego Maradona	Diego	Maradona

Next, how to join or combine contents of two cells or more into one cell?

To join or combine contents, Excel has a formula that is CONCATENATE. Concatenate is used to join several text strings into one text string. For example, we would like to rejoin the data we’ve separated on explanation above, type a formula:

=CONCATENATE([cell reference];" ";[cell reference])

Cell reference means reference of the cell you want to join. Note that you also need to add a space between two quotations so you’ll get spaced results.

Remarks: Instead of using Concatenate, the “&” operator can be used to join text items, and the formula would be:

=[cell reference]&" "&[cell reference]

example:

=A1&" "&B1 

The two formulas above will give same result.

Comment please...!