I know the short way to concatenate two or three cells, but how can I do a
concatenate job for a column of 500 cells in an easier way instead of keying
in the statement one by one?
-Chris

Chris108 wrote...
I know the short way to concatenate two or three cells, but how can I do a
concatenate job for a column of 500 cells in an easier way instead of keying
in the statement one by one?

The only way to do this is using a user-defined function (udf) written
in VBA. [Add-in XLL functions won't handle returning more than 255
chars in Excel 2003 and prior just in case anyone wants to quibble.]

One general udf to do this is shown in the following article in the
archives.

http://groups.google.com/group/micro...456a9e326b19a6

You'd need to add the code to a general module. To do that, press the
[Alt]+[F11] key combination to launch the Visual Basic Editor, run the
menu command Insert Module, then paste the code from the linked
article into that module.

You can't do that.
Excel only lets you concatenate 30 text strings.
At 500 cells, if the formula was possible, you might even run into the
character limit for a single cell.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Chris108" wrote:

I know the short way to concatenate two or three cells, but how can I do a
concatenate job for a column of 500 cells in an easier way instead of keying
in the statement one by one?
-Chris

Excel 2007 Help refers to 255 as a limit

The CONCATENATE function joins up to 255 text strings into one text
string. The joined items can be text, numbers, cell references, or a
combination of those items. For example, if your worksheet contains a
person's first name in cell A1 and the person's last name in cell B1,
you can combine the two values in another cell by using the following
formula:



Chris108 wrote:

I know the short way to concatenate two or three cells, but how can I do a
concatenate job for a column of 500 cells in an easier way instead of keying
in the statement one by one?
-Chris

Are you trying to combine all the text in those 500 cells into a
single cell value?

The CONCATENATE function is rather useless. For a better concatenation
function, use the code at
http://www.cpearson.com/excel/stringconcatenation.aspx

and then call StringConcat from a worksheet cell:

=StringConcat(" ",A1:A100)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 15 Dec 2008 10:11:01 -0800, Chris108
wrote:

I know the short way to concatenate two or three cells, but how can I do a
concatenate job for a column of 500 cells in an easier way instead of keying
in the statement one by one?
-Chris