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?