Multiple line-itmes

  • Thread starter Thread starter tester
  • Start date Start date
T

tester

I am working with an Excel spreadsheet that shows names
in columns A through line 200.

ie:
John
Howard
Bill
Jose

I would like to create a formula that puts those four
names into one cell:

ie: John Howard Bill Jose
 
Check out help info for 'concatenate'

=concatenate(a1," ",a2," ",a3," ",a4, .... )

Assuming you want spaces between the names.
 
Example of what I tried.

=CONCATENATE
(A3," ",A4," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",
A11, " ", A12, " ", A13, " ", A14, " ", A15, " ",A16)

Problem being, there are 300+ plus lines on the worksheet.
Is there a better way of doing this without having to
enter each cell by hand - thanks again!
 
Example of what I tried.

=CONCATENATE
(A3," ",A4," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",
A11, " ", A12, " ", A13, " ", A14, " ", A15, " ",A16)

Problem being, there are 300+ plus lines on the worksheet.
Is there a better way of doing this without having to
enter each cell by hand - thanks again!
 
Example of what I tried.

=CONCATENATE
(A3," ",A4," ",A5," ",A6," ",A7," ",A8," ",A9," ",A10," ",
A11, " ", A12, " ", A13, " ", A14, " ", A15, " ",A16)

Problem being, there are 300+ plus lines on the worksheet.
Is there a better way of doing this without having to
enter each cell by hand - thanks again!
Click to expand...

If you're willing to use an add-in or VBA, yes. Otherwise, no. The best add-in
for this is Laurent Longre's MOREFUNC.XLL, which is available from

http://longre.free.fr/english/

It contains a function named MCONCAT which you could use as

=TRIM(MCONCAT(A3:A302&" "))
 
thanks for the formula - I went to the web site you
provided and made the download.

One more question: what is the max amount of data that
Excel allows in a single column - thanks!
 
...
...
One more question: what is the max amount of data that
Excel allows in a single column - thanks!
Click to expand...
...

Theoretically, 65536 cells in a column each containing 32767 characters, or
2,147,418,112 characters. Since that's much more than available memory
(including virtual memory) on most current PCs, it's an impractical limit.
 
Back
Top