View Full Version : Solved: Combine text data from 9 cells into 1 cell

01-30-2008, 06:31 PM
I am using excel version 2000. sp-3
I need to learn how to combine date from 9 cells in a row into a single cell. Some of the cell in the row may not have any data and some cell may have data in up to 4 different cells.
In the example I have attached.
Cell AI6 is the cell were I would like the combined data to appear.
The source cells are S6:AA6. as you can see my attempt using:
To combine text from multiple cells into one cell, use the & (ampersand) operator.
did not work.

Any help or ideas would be helpful

Mike in wisconsin

01-30-2008, 06:53 PM
Formatting a cell as TEXT frequently causes problems.

There were a couple of missing ampersands.
=S6&" "&T6&" "&U6&""&V6&" "&W6&" "&X6&" "&Y6&" "&Z6&" "&AA6

01-30-2008, 07:19 PM
The missing ampersands did not fix the problem!

Mike in wisconsin

01-30-2008, 07:34 PM
After looking at my spreadsheet again.

The option Combine Text from Multiple Cells
To combine text from multiple cells into one cell, use the & (ampersand) operator.

Select the cell in which you want the combined data
Type an = (equal sign) to start the formula
Click on the first cell
Type the & operator (shift + 7)
Click on the second cell
Press Enter to complete the formulaDoes not work.

and the =CONCATENATE(S6,T6,U6,V6,W6) only works part opf the way. Only 5 cell can be referenced and any empty cell inserts a 0 for that cell. I cna do what I want in 3 cell two to get the dat and one to combine the t colection cell. I need to figure how to drop all the zeros.

01-30-2008, 08:15 PM
Is this it?

01-31-2008, 07:02 AM
John, that did it. It works beyond belief.

Thank you so much