I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

You are aware that a cell can take in limited number of characters. Any more
than that number, the result will be truncated please.

"mj44" wrote in message
...
I have a spreadsheet using 3 columns and n number of rows worth of data.
I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Presuming that you need comma seperators, in D1 put

=A1&","&B1&","&C1

and in D2 put

=D1&","&A2&","&B2&","&C2

and formula-drag that to the end of your data.

in the next cell I suggest you put

=mid(D9999,32700,1024)

to display the last 68 characters of the permitted 32767 limit, if you
have data here you have probably exceeded the limit, backtrack up the
data and decide where to re-start the formula by removing the Dnn&","
from the start.
You may need multiple sets if you have more than 32,000 characters.
note, the D9999 reference means the previous cell, ie the last one
filled with data.

After you have the data collated, you might want to Copy and Paste
Special = Values for the final cell(s) to preserve the data.



mj44 Wrote:
I have a spreadsheet using 3 columns and n number of rows worth of data.
I
am trying to create one long string of this data by concatenating in
the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382

Using this hint, we suggest copy D1 to E1
put E2=E1 & D2
copy all the way down
we can now visually inspect where E# starts to truncate data and adjust
accordingly.

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1yd7gy_1131767101.0139@excelforu m-nospam.com...

Presuming that you need comma seperators, in D1 put

=A1&","&B1&","&C1

and in D2 put

=D1&","&A2&","&B2&","&C2

and formula-drag that to the end of your data.

in the next cell I suggest you put

=mid(D9999,32700,1024)

to display the last 68 characters of the permitted 32767 limit, if you
have data here you have probably exceeded the limit, backtrack up the
data and decide where to re-start the formula by removing the Dnn&","
from the start.
You may need multiple sets if you have more than 32,000 characters.
note, the D9999 reference means the previous cell, ie the last one
filled with data.

After you have the data collated, you might want to Copy and Paste
Special = Values for the final cell(s) to preserve the data.



mj44 Wrote:
I have a spreadsheet using 3 columns and n number of rows worth of data.
I
am trying to create one long string of this data by concatenating in
the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382

OK - I guess that beats me, as
E1 will then = A1,B1,C1
E2 will then = A1,B1,C1A1,B1,C1,A2,B2,C2
E3 will then = A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3
E4 will then =
A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3A1,B1,C1,A2,B2,C2,A3,B3,C3,A4,B4,C4


and the progression gets increasingly further from what the OP
requested.
It is (almost) possible to visually inspect, but of 32,767 characters
only 1024 are displayed in the row, the full set is displayed only in
the formula bar.

A better suggestion for checking might be that E1 be

=Right(D1,40)

and formula copy that to the end of data rows, at the point of
overflow the E column ceases changing and displays the same characters
32,728 to 32,767



PY & Associates Wrote:
Using this hint, we suggest copy D1 to E1
put E2=E1 & D2
copy all the way down
we can now visually inspect where E# starts to truncate data and
adjust
accordingly.

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1yd7gy_1131767101.0139@excelforu m-nospam.com...

Presuming that you need comma seperators, in D1 put

=A1&","&B1&","&C1

and in D2 put

=D1&","&A2&","&B2&","&C2

and formula-drag that to the end of your data.

in the next cell I suggest you put

=mid(D9999,32700,1024)

to display the last 68 characters of the permitted 32767 limit, if
you
have data here you have probably exceeded the limit, backtrack up
the
data and decide where to re-start the formula by removing the
Dnn&","
from the start.
You may need multiple sets if you have more than 32,000 characters.
note, the D9999 reference means the previous cell, ie the last one
filled with data.

After you have the data collated, you might want to Copy and Paste
Special = Values for the final cell(s) to preserve the data.



mj44 Wrote:
I have a spreadsheet using 3 columns and n number of rows worth of
data.
I
am trying to create one long string of this data by concatenating
in
the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually
click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=484382



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382

Sorry, I looked at your post again and admit I have missed D1 as in
=D1&","&A2&","&B2&","&C2

My suggestion is therefore redundant.

For my curiosity, where is the 32,767 characters come from please?

"Bryan Hessey"
wrote in message
news:Bryan.Hessey.1ydc3m_1131773100.8989@excelforu m-nospam.com...

OK - I guess that beats me, as
E1 will then = A1,B1,C1
E2 will then = A1,B1,C1A1,B1,C1,A2,B2,C2
E3 will then = A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3
E4 will then =

A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3A1,B1,C1,A2,B2,C2,A3,B3,C
3,A4,B4,C4


and the progression gets increasingly further from what the OP
requested.
It is (almost) possible to visually inspect, but of 32,767 characters
only 1024 are displayed in the row, the full set is displayed only in
the formula bar.

A better suggestion for checking might be that E1 be

=Right(D1,40)

and formula copy that to the end of data rows, at the point of
overflow the E column ceases changing and displays the same characters
32,728 to 32,767



PY & Associates Wrote:
Using this hint, we suggest copy D1 to E1
put E2=E1 & D2
copy all the way down
we can now visually inspect where E# starts to truncate data and
adjust
accordingly.

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1yd7gy_1131767101.0139@excelforu m-nospam.com...

Presuming that you need comma seperators, in D1 put

=A1&","&B1&","&C1

and in D2 put

=D1&","&A2&","&B2&","&C2

and formula-drag that to the end of your data.

in the next cell I suggest you put

=mid(D9999,32700,1024)

to display the last 68 characters of the permitted 32767 limit, if
you
have data here you have probably exceeded the limit, backtrack up
the
data and decide where to re-start the formula by removing the
Dnn&","
from the start.
You may need multiple sets if you have more than 32,000 characters.
note, the D9999 reference means the previous cell, ie the last one
filled with data.

After you have the data collated, you might want to Copy and Paste
Special = Values for the final cell(s) to preserve the data.



mj44 Wrote:
I have a spreadsheet using 3 columns and n number of rows worth of
data.
I
am trying to create one long string of this data by concatenating
in
the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually
click on
each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=484382



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382

In the help, search for Limit and look up
Excel specifications and limits
Worksheet and workbook specifications
which includes:

Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar.


PY & Associates Wrote:[color=blue]
Sorry, I looked at your post again and admit I have missed D1 as in
=D1&","&A2&","&B2&","&C2

My suggestion is therefore redundant.

For my curiosity, where is the 32,767 characters come from please?

"Bryan Hessey"

wrote in message
news:Bryan.Hessey.1ydc3m_1131773100.8989@excelforu m-nospam.com...

OK - I guess that beats me, as
E1 will then = A1,B1,C1
E2 will then = A1,B1,C1A1,B1,C1,A2,B2,C2
E3 will then = A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3
E4 will then =

A1,B1,C1A1,B1,C1,A2,B2,C2A1,B1,C1,A2,B2,C2,A3,B3,C 3A1,B1,C1,A2,B2,C2,A3,B3,C
3,A4,B4,C4


and the progression gets increasingly further from what the OP
requested.
It is (almost) possible to visually inspect, but of 32,767
characters
only 1024 are displayed in the row, the full set is displayed only
in
the formula bar.

A better suggestion for checking might be that E1 be

=Right(D1,40)

and formula copy that to the end of data rows, at the point of
overflow the E column ceases changing and displays the same
characters
32,728 to 32,767


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=484382

On Fri, 11 Nov 2005 11:56:07 -0800, "mj44"
wrote:

I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?
--
Thank you,
mj44


"Ron Rosenfeld" wrote:

On Fri, 11 Nov 2005 11:56:07 -0800, "mj44"
wrote:

I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron

On Mon, 14 Nov 2005 05:37:08 -0800, "mj44"
wrote:

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?

Only if you did not download and install morefunc.xll.


--ron

I have not done that. Do you know where and how I can find and install
morefunc.xll?
--
Thank you,
mj44


"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 05:37:08 -0800, "mj44"
wrote:

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?

Only if you did not download and install morefunc.xll.


--ron

Per Ron's original post, download the add-in he

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/



--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=484382

From Ron's post:

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

mj44 wrote:

I have not done that. Do you know where and how I can find and install
morefunc.xll?
--
Thank you,
mj44

"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 05:37:08 -0800, "mj44"
wrote:

Ron,
When I try using =mconcat(a1:c500), my result is #NAME? Why would I get
this as a result?

Only if you did not download and install morefunc.xll.


--ron


--

Dave Peterson

On Mon, 14 Nov 2005 05:51:02 -0800, "mj44"
wrote:

I have not done that. Do you know where and how I can find and install
morefunc.xll?


What happened when you tried the method I outlined in my first response to your
request?


--ron

I was also interested in using the MCONCAT formula your giving below but have
the following error message when accessing the web site:
"You don't have permission to access / on this server."

Would you know why?
Do you know if it is available from another web site?

Thanks

"Ron Rosenfeld" wrote:

On Fri, 11 Nov 2005 11:56:07 -0800, "mj44"
wrote:

I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron

Use this UDF which will do the same thing.

Will leave a space between data from each cell.

Ignores blank cells.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:C100)

Or a macro which leaves no formulas to deal with.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells..Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Also ignores blank cells.


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 08:18:02 -0700, Bonobo
wrote:

I was also interested in using the MCONCAT formula your giving below but have
the following error message when accessing the web site:
"You don't have permission to access / on this server."

Would you know why?
Do you know if it is available from another web site?

Thanks

"Ron Rosenfeld" wrote:

On Fri, 11 Nov 2005 11:56:07 -0800, "mj44"
wrote:

I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron

The function works perfectly.
Thanks!

"Gord Dibben" wrote:

Use this UDF which will do the same thing.

Will leave a space between data from each cell.

Ignores blank cells.

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sbuf As String
For Each cell In CellBlock
If Len(cell.text) 0 Then sbuf = sbuf & cell.text & " "
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function

=ConCatRange(A1:C100)

Or a macro which leaves no formulas to deal with.

Sub ConCat_Cells()
Dim x As Range
Dim y As Range
Dim z As Range
Dim w As String
Dim sbuf As String
On Error GoTo endit
w = InputBox("Enter the Type of De-limiter Desired")
Set z = Application.InputBox("Select Destination Cell", _
"Destination Cell", , , , , , 8)
Application.SendKeys "+{F8}"
Set x = Application.InputBox("Select Cells..Contiguous or Non-Contiguous", _
"Cells Selection", , , , , , 8)
For Each y In x
If Len(y.text) 0 Then sbuf = sbuf & y.text & w
Next
z = Left(sbuf, Len(sbuf) - 1)
Exit Sub
endit:
MsgBox "Nothing Selected. Please try again."
End Sub

Also ignores blank cells.


Gord Dibben MS Excel MVP

On Wed, 18 Jul 2007 08:18:02 -0700, Bonobo
wrote:

I was also interested in using the MCONCAT formula your giving below but have
the following error message when accessing the web site:
"You don't have permission to access / on this server."

Would you know why?
Do you know if it is available from another web site?

Thanks

"Ron Rosenfeld" wrote:

On Fri, 11 Nov 2005 11:56:07 -0800, "mj44"
wrote:

I have a spreadsheet using 3 columns and n number of rows worth of data. I
am trying to create one long string of this data by concatenating in the
following order:

A1,B1,C1,A2,B2,C2,A3,B3,C3, etc.

The only way I know how to do a large range is to individually click on each
cell and that will take a very long time.

Is there an easy way to concatenate a range such as (A1:C500)?

Thank you.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use the formula:

=MCONCAT(A1:C500,", ")

Be aware of this Excel specification, though:

Length of cell contents (text)
32,767 characters.
Only 1,024 display in a cell; all 32,767 display in the formula bar.


--ron