Combine multiple text cells into one cell (separating the texts by a semicolon)

[aris1981]

For example:

Cell A1: Robert
Cell A2: John
Cell A3: Will

Cell B1 (combine the 3 texts automatically, separated by semicolon): Robert;John;Will

Thank you.

 

[gaz_chops]

Try
=a1&";"&a2&";"&a3

 

[aris1981]

Thank you! Is there any way to automate the a1, a2, a3 to continue i.e. until a100

 

[aris1981]

I have a list of hundreds of cells, and want to combine them, and thus was looking for an automated solution rather than manual entry.​

Any ideas?

Thanks!

 

[DocAElstein]

I have a list of hundreds of cells, and want to combine them, and thus was looking for an automated solution rather than manual entry.​

Any ideas?

Thanks!

Hi Arris/ArmyTray

Before:

Excel 2010
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]Tom1[/td][td]Dick1[/td][td]Arries1[/td][/tr]

[tr][td]

2​

[/td][td]Tom2[/td][td]Dick2[/td][td]Arries2[/td][/tr]

[tr][td]

3​

[/td][td]Tom3[/td][td]Dick3[/td][td]Arries3[/td][/tr]

[tr][td]

4​

[/td][td]Tom4[/td][td]Dick4[/td][td]Arries4[/td][/tr]

[tr][td]

5​

[/td][td]Tom5[/td][td]Dick5[/td][td]Arries5[/td][/tr]

[tr][td]

6​

[/td][td]Tom6[/td][td]Dick6[/td][td]Arries6[/td][/tr]

[tr][td]

7​

[/td][td]Tom7[/td][td]Dick7[/td][td]Arries7[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Aris[/td][/tr][/table]

After:

Excel 2010
[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Tom1[/td][td]Dick1[/td][td]Arries1[/td][td]Tom1 ; Dick1 ; Arries1[/td][/tr]

[tr][td]

2​

[/td][td]Tom2[/td][td]Dick2[/td][td]Arries2[/td][td]Tom2 ; Dick2 ; Arries2[/td][/tr]

[tr][td]

3​

[/td][td]Tom3[/td][td]Dick3[/td][td]Arries3[/td][td]Tom3 ; Dick3 ; Arries3[/td][/tr]

[tr][td]

4​

[/td][td]Tom4[/td][td]Dick4[/td][td]Arries4[/td][td]Tom4 ; Dick4 ; Arries4[/td][/tr]

[tr][td]

5​

[/td][td]Tom5[/td][td]Dick5[/td][td]Arries5[/td][td]Tom5 ; Dick5 ; Arries5[/td][/tr]

[tr][td]

6​

[/td][td]Tom6[/td][td]Dick6[/td][td]Arries6[/td][td]Tom6 ; Dick6 ; Arries6[/td][/tr]

[tr][td]

7​

[/td][td]Tom7[/td][td]Dick7[/td][td]Arries7[/td][td]Tom7 ; Dick7 ; Arries7[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Aris[/td][/tr][/table]

Code:

[color=blue]Option[/color] [color=blue]Explicit[/color]
[color=blue]Sub[/color] arisConcatenating()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=lightgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") [color=lightgreen]'Output Range[/color]

[color=lightgreen]'Input Ranges[/color]
[color=blue]Dim[/color] rngA [color=blue]As[/color] Range: [color=blue]Set[/color] rngA = wks.Range("A1:A" & lr & "")
[color=blue]Dim[/color] rngB [color=blue]As[/color] Range: [color=blue]Set[/color] rngB = wks.Range("B1:B" & lr & "")
[color=blue]Dim[/color] rngC [color=blue]As[/color] Range: [color=blue]Set[/color] rngC = wks.Range("C1:C" & lr & "")

[color=lightgreen]'Conctente with Evaluate[/color]
[color=blue]Let[/color] rngD.Value = Evaluate("" & rngA.Address & "" & "&"" ; ""&" & " " & rngB.Address & "" & "&"" ; ""&" & " " & rngC.Address & "")

[color=blue]End[/color] [color=blue]Sub[/color] [color=lightgreen]'arisConcatenating[/color]

Full Explanation Here:
http://www.mrexcel.com/forum/excel-...nto-single-column-using-data-text-column.html
And Here
Multiple Columns Into Single Column Using Data Text To Column - Page 2

Need any more help, how to get stated with Macros etc., then get back..

Alan

 

[DocAElstein]

. (This post duplicated in similar Thread here:
http://www.mrexcel.com/forum/excel-...te-multiple-rows-columns-my-case-100-a-2.html
… where many other code variations from me, Rick Rothstein and others are given )

……………………….

. Hi,
. … this is just a very quick follow up, which came out of another Thread I was answering:
Excel Marco How to put multiple rows text values into single cell using macro
so I thought in parsing I would drop it in here:

.. Maybe this code version could be useful if a hundred or so columns were being used. The virtue here is that the String argument that would be used in the “Range Evaluate one – liner “ is made in a loop so avoids you having to type manually the long evaluate string argument necessary for many columns..

[color=darkgreen]'[/color]
[color=blue]Sub[/color] ArrisArmyTrayConcatenating3()
[color=blue]Dim[/color] wks [color=blue]As[/color] Worksheet: [color=blue]Set[/color] wks = ThisWorkbook.Worksheets("Aris") [color=darkgreen]'Sheet Info[/color]
[color=blue]Dim[/color] lr [color=blue]As[/color] Long: [color=blue]Let[/color] lr = wks.Cells(Rows.Count, 1).End(xlUp).Row 'Last Row in Sheet
[color=blue]Dim[/color] r [color=blue]As[/color] [color=blue]Long[/color] [color=darkgreen]'Rows in Sheet[/color]
[color=blue]Dim[/color] rngD [color=blue]As[/color] Range: [color=blue]Set[/color] rngD = wks.Range("D1:D" & lr & "") 'Output Range
  rngD.Clear [color=darkgreen]'Just so I know the conctnating lines work!![/color]
[color=blue]Dim[/color] Evalstr [color=blue]As[/color] [color=blue]String[/color] [color=darkgreen]'String argument for Evaluate "One Liner[/color]
 
[color=darkgreen]' Build string for Evalute "one liner"[/color]
 [color=blue]Dim[/color] c [color=blue]As[/color] [color=blue]Long[/color], lc [color=blue]As[/color] Long: [color=blue]Let[/color] lc = 3 [color=darkgreen]'Column, and last column[/color]
    [color=blue]For[/color] c = 1 [color=blue]To[/color] lc - 1 [color=blue]Step[/color] 1
    [color=blue]Let[/color] Evalstr = Evalstr & "" & wks.Range(wks.Cells(1, c), wks.Cells(lr, c)).Address & "" & "&"" ; ""&" [color=darkgreen]'Concatenate cell values with  ; inbetween[/color]
    [color=blue]Next[/color] c
 [color=blue]Let[/color] Evalstr = Evalstr & "" & wks.Range(wks.Cells(1, lc), wks.Cells(lr, lc)).Address & "" [color=darkgreen]'Concatenate last row without any  ;[/color]
 
[color=darkgreen]'Concatenate with Evaluate[/color]
 [color=blue]Let[/color] rngD.Value = Evaluate(Evalstr)
[color=blue]End[/color] [color=blue]Sub[/color] [color=darkgreen]'ArrisArmyTrayConcatenating3[/color]

Alan

P.s. The limit of about 100 columns is due to the evaluate argument character limit of 255, so depending on how long your strings in the cells are, your column number will be reduced (In the other Thread I was limited to 5 ! -- So the code was not much use there!! But those strings were very long!!! – For sensible length strings in the cells the code may be useful. Hence I have included it here!

 

[garcianostephen]

For example:

Cell A1: Robert
Cell A2: John
Cell A3: Will

Cell B1 (combine the 3 texts automatically, separated by semicolon): Robert;John;Will

Thank you.

Try using =textjoin(";",True,A1:A3)