Data in a Column to a Single Cell Separated by Commas – Macro VBA
This macro is useful in case you have a data table in the following format and you want to transform it to a single cell separated by commas.
43680 |
43681 |
43659 |
43677 |
43578 |
43522 |
Preferred Results:
43680;43681;43659;43677;43578;43522 |
Excel Macro:
Option 1:
Sub ConcatColumnValues() Range("B1") = Join(Application.Transpose(Range("A1", Cells(Rows.Count, "A").End(xlUp))), ";") End Sub
Option 2:
Public Sub TextEr() rs = Rows.Count lr = Cells(rs, 1).End(xlUp).Row stxt = "" For Each oj In Range("A1:A" & lr) If stxt = "" Then stxt = oj Else stxt = stxt & ";" & oj End If Next oj Range("b1").NumberFormat = "@" Range("b1") = stxt End Sub