Automatically merge mulitiple cells to one cells

[Edward Wang]

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?

 

[Jacob Skaria]

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

 

[Ashish Mathur]

Hi,

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

=MCONCAT(A1:A5,";")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Edward Wang]

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]

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

 

[Edward Wang]

it works! Thanks!

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