Automatically merge mulitiple cells to one cells
 

Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them into
one cell and insert ";" in the between. The number of cells will be dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
....
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?

Automatically merge mulitiple cells to one cells
 

You will have to use a UDF or an Add-In to acheive this. Try this UDF (User
Defined function). From workbook launch VBE using Alt+F11. From menu Insert a
Module and paste the below function.Close and get back to workbook and try
the below formula.

=CONCATRANGE(A1:A5,";")

Function CONCATRANGE(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
CONCATRANGE = CONCATRANGE & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them into
one cell and insert ";" in the between. The number of cells will be dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
...
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?

Automatically merge mulitiple cells to one cells
 

Hi,

Download and install the following addin
http://www.download.com/Morefunc/300...-10423159.html and then use the
MCONCAT() function

=MCONCAT(A1:A5,";")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Edward Wang" wrote in message
...
Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them
into
one cell and insert ";" in the between. The number of cells will be
dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
...
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?

Automatically merge mulitiple cells to one cells
 

Thank you, Jacob! It works!

A further question, if I have another column beside to column A, like below.
I want to merge the Text of Column A based on the condition of Column B.
Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238"
Column A Column B
8R1234 A
8R1235 B
8R1236 A
8R1237 B
8R1238 A

Thanks in advance!

"Jacob Skaria" wrote:

You will have to use a UDF or an Add-In to acheive this. Try this UDF (User
Defined function). From workbook launch VBE using Alt+F11. From menu Insert a
Module and paste the below function.Close and get back to workbook and try
the below formula.

=CONCATRANGE(A1:A5,";")

Function CONCATRANGE(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
CONCATRANGE = CONCATRANGE & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them into
one cell and insert ";" in the between. The number of cells will be dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
...
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?

Automatically merge mulitiple cells to one cells
 

Try the below...(Taken from an earlier post of mine)

=CONCATBY($A$1:$B$5,A1,2)

$A$1:$B$5 - Array
A1 - lookup
2 - Column to be combined..


Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function CONCATBY(varRange As Range, _
strData As String, intCol As Integer)
Dim lngRow As Long
For lngRow = 1 To varRange.Rows.Count
If varRange(lngRow, 1) = strData Then
CONCATBY = CONCATBY & "," & varRange(lngRow, intCol)
End If
Next
CONCATBY = Mid(CONCATBY, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Thank you, Jacob! It works!

A further question, if I have another column beside to column A, like below.
I want to merge the Text of Column A based on the condition of Column B.
Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238"
Column A Column B
8R1234 A
8R1235 B
8R1236 A
8R1237 B
8R1238 A

Thanks in advance!

"Jacob Skaria" wrote:

You will have to use a UDF or an Add-In to acheive this. Try this UDF (User
Defined function). From workbook launch VBE using Alt+F11. From menu Insert a
Module and paste the below function.Close and get back to workbook and try
the below formula.

=CONCATRANGE(A1:A5,";")

Function CONCATRANGE(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
CONCATRANGE = CONCATRANGE & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them into
one cell and insert ";" in the between. The number of cells will be dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
...
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?

Automatically merge mulitiple cells to one cells
 

it works! Thanks!

I just made two minimum changes:
1. using ";" in the between
2. using "instr" function to replace "=" logical judgement

"Jacob Skaria" wrote:

Try the below...(Taken from an earlier post of mine)

=CONCATBY($A$1:$B$5,A1,2)

$A$1:$B$5 - Array
A1 - lookup
2 - Column to be combined..


Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

Function CONCATBY(varRange As Range, _
strData As String, intCol As Integer)
Dim lngRow As Long
For lngRow = 1 To varRange.Rows.Count
If varRange(lngRow, 1) = strData Then
CONCATBY = CONCATBY & "," & varRange(lngRow, intCol)
End If
Next
CONCATBY = Mid(CONCATBY, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Thank you, Jacob! It works!

A further question, if I have another column beside to column A, like below.
I want to merge the Text of Column A based on the condition of Column B.
Say if I only Pick A from Column B, the result will be "8R1234;8R1236;8R1238"
Column A Column B
8R1234 A
8R1235 B
8R1236 A
8R1237 B
8R1238 A

Thanks in advance!

"Jacob Skaria" wrote:

You will have to use a UDF or an Add-In to acheive this. Try this UDF (User
Defined function). From workbook launch VBE using Alt+F11. From menu Insert a
Module and paste the below function.Close and get back to workbook and try
the below formula.

=CONCATRANGE(A1:A5,";")

Function CONCATRANGE(varRange As Range, Optional varDelimiter As String)
Dim varTemp As Range
For Each varTemp In varRange
CONCATRANGE = CONCATRANGE & varDelimiter & varTemp
Next
If varDelimiter < vbNullString Then CONCATRANGE = Mid(CONCATRANGE, 2)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Edward Wang" wrote:

Any one can help me out here? Preferably without VB.

I have a bunch of continues cells in one column, and want to merge them into
one cell and insert ";" in the between. The number of cells will be dynamic.
Like
A1 8R1234
A2 8R1235
A3 8R1236
A4 8R1237
A5 8R1238
...
Result cell = 8R1234;8R1235;8R1236;8R1237;8R1238;...

As the result will be used in another function, I do not want to use one
spare column beside the data to build up that text string one by one.

Any advice?