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
Asked
Active
Viewed 784 times
1 Answers
4
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()
Gary's Student
- 95,722
- 10
- 59
- 99
-
1I'm using Excel 365 and your formula worked perfectly! Thank you so much for your help!! Appreciate it! – Hershey672 Oct 25 '20 at 17:05
-
So you transpose the range (in order to run through the whole thing in the right order), then you concatenate everything, but then what? Why do you need those `` and other tags for, and what does that `FilterXML()` function exactly do? – Dominique Oct 25 '20 at 17:22
-
@Dominique See my added **Notes**. – Gary's Student Oct 25 '20 at 18:20
-
1To get the result in one cell, for 'semi-colon' users: `=FILTERXML(""&SUBSTITUTE(TEXTJOIN(",";TRUE;TRANSPOSE($A$2:$J$999));";";"")&"";"//b")`, where it is kind of hard to figure out the middle semi-colon: `);";";"<` (at least it was for me). In Excel 2019 entered as an array formula (`CSE`). Still haven't figured out how to get the column spill. – VBasic2008 Oct 25 '20 at 19:57
-
@VBasic2008 ______________________Thanks! – Gary's Student Oct 25 '20 at 20:07