Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

You could put this in B1:

=A1

and then put this in B2

=B1&A2

Copy this down to B30, and this final cell will contain the composite
string.

Hope this helps.

Pete

On Nov 1, 10:55*am, George A. Jululian
wrote:
Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

we could even expand on that to give it the ability to put a "delimiter" or
separator between each non-empty cell's values in the result:

Function concatplus(r As Range) As String
Dim rr As Range
Const delimiter = " " ' could change to ", "," if desired
concatplus = ""
For Each rr In r
If Not IsEmpty(rr) Then
concatplus = concatplus & rr.Value & delimiter
End If
Next
If Len(concatplus) 0 Then
'remove last, unnecessary delimiter
concatplus = Left(concatplus, _
Len(concatplus) - Len(delimiter))
End If
End Function


"Gary''s Student" wrote:

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

First of all many thank

please advice where i wirte them or how can use the

regards


"JLatham" wrote:

we could even expand on that to give it the ability to put a "delimiter" or
separator between each non-empty cell's values in the result:

Function concatplus(r As Range) As String
Dim rr As Range
Const delimiter = " " ' could change to ", "," if desired
concatplus = ""
For Each rr In r
If Not IsEmpty(rr) Then
concatplus = concatplus & rr.Value & delimiter
End If
Next
If Len(concatplus) 0 Then
'remove last, unnecessary delimiter
concatplus = Left(concatplus, _
Len(concatplus) - Len(delimiter))
End If
End Function


"Gary''s Student" wrote:

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

First of all many thank

please advice where i wirte them or how can use them

regards

"Gary''s Student" wrote:

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

Many Thanks

i wrote them all

many thanks agian



"JLatham" wrote:

we could even expand on that to give it the ability to put a "delimiter" or
separator between each non-empty cell's values in the result:

Function concatplus(r As Range) As String
Dim rr As Range
Const delimiter = " " ' could change to ", "," if desired
concatplus = ""
For Each rr In r
If Not IsEmpty(rr) Then
concatplus = concatplus & rr.Value & delimiter
End If
Next
If Len(concatplus) 0 Then
'remove last, unnecessary delimiter
concatplus = Left(concatplus, _
Len(concatplus) - Len(delimiter))
End If
End Function


"Gary''s Student" wrote:

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards

Many Thanks

i wrote them all

many thanks agian



"Gary''s Student" wrote:

It would be nice if there was a function like =SUM() that would concatenate
vlues rather than sum them. We can make such a function (User Defined
Function):

Function concat(r As Range) As String
Dim rr As Range
concat = ""
For Each rr In r
concat = concat & rr.Value
Next
End Function



User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=concat(A1:A30)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200908


"George A. Jululian" wrote:

Dear All,

I have one colum and 30 rows
and i want to put all in one cell and i am using
A1&A2&A3

please advice if there is any other soultion

regards