Friday, February 6, 2009

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 NameFirst NameLast 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 NameFirst NameLast Name
Barack ObamaBarackObama
Michael BoltonMichaelBolton
John OutlookJohnOutlook
Cameron DiazCameronDiaz
Diego MaradonaDiegoMaradona

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]


=A1&" "&B1 

The two formulas above will give same result.

Comment please...!

Comments :

0 comments to “Divide, Combine and Join Cell Content in Excel”


Published by COMPUTER STUFF supported by DVD Writer USB