I have 10 columns of different values that I would like to merge into one column. Can this be done by an Excel formula? I've read that it is possible through VBA but I'm not particular with that. Here is a screenshot of my sample data for easier visualization. Stack into one column
1 Answers
Using Excel 365:
In K2 enter:
=FILTERXML("<a><b>"&SUBSTITUTE(TEXTJOIN(",",TRUE,TRANSPOSE($A$2:$J$999)),",","</b><b>")&"</b></a>","//b")
(Should work in Excel 2013 and forward, but may require array-entry)
Notes:
The TEXTJOIN() creates a comma-separated string from the block of cells in the proper order without empties.
See jvdV's post for tutorial material on FILTERXML()
Related