In Excel 2013 I need to have the content of the cells on the array A9:E18 listed in the cells from H1 to H10 (because I already know that there will always be exactly 10 non-blank cells in that array, the rest of them being empty). I cannot find a proper formula to have it done. Could anybody help me, please?
Asked
Active
Viewed 227 times
0
-
1Can post some sample data? – Harun24hr Apr 05 '21 at 07:21
-
There are no data yet, I'm simply building the structure of it. The cells will be populated by text values. Perhaps I haven't explained correctly the situation, my bad. The array A9:E18 will have only empty cells BUT 10 of them which will be populated by text values. I need to have these 10 values to be listed from H1 to H10. – frons79 Apr 05 '21 at 07:52
-
Can you please check my answer? – Harun24hr Apr 05 '21 at 08:52
1 Answers
1
I would suggest you to use FILTERXML() with TEXTJOIN() function. Unfortunately Excel2013 doesn't have TEXTJOIN() function. You can use a custom TEXTJOIN() function from this article TextJoin UDF For Excel 2013
of @Scott Craner answer. Then use FILTERXML() like below.
=IFERROR(INDEX(FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$E$18)&"</s></t>","//s"),ROW($A1)),"")
With Excel365 you can simply use-
=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",TRUE,$A$1:$E$18)&"</s></t>","//s")
Harun24hr
- 30,391
- 4
- 21
- 36
-
Unfortunately it's not properly working, just giving me back only 3 results out of the expected 10. – frons79 Apr 05 '21 at 13:50
-
-
This one, =IFERROR(INDEX(FILTERXML("
"&TEXTJOIN("",TRUE,$A$1:$E$18)&" -
Fixed the error myself, I had modified ROW($A1) into ROW($A9) where it needed to stay that way. Now it works, thank you a lot for your help :) – frons79 Apr 05 '21 at 15:23
-