Forum Discussion
Solved
Prevent Spilled Array
PC/Windows 10
Excel Version 2303 Build 16.0.16227.20202 64-bit
I want to list out a range of cells (D36:D63) but when I do, Excel spills the array into blank cells underneath. My work around is to list every cell (D36&D37&D38&D39&D40&D41&D42&D43....) This is not going to work well as I add more data. I also have the cell adding text before and after the cells.
I have tried to use @ but that will just show the cell to the left only.
Code I want to work:
="mods="""&D36:D63&""""
My current workaround: ="mods="""&D36&D37&D38&D39&D40&D41&D42&D43&D44&D45&D46&D47&D48&D49&D50&D51&D52&D53&D54&D55&D56&D57&D58&D59&D60&D61&D62&D63&D66&D67&D68&D71&D72&""""
assuming you have 365 then you can use TEXTJOIN and even give deliminator like a comma ","
if you don't have access to TEXTJOIN you can use CONCAT(D36:D72 & ","), but I think TEXTJOIN and CONCAT we added at the same time but it is possible a copy of excel 2019 might have concat and not textjoin I suppose.I just noticed you skip a few cells so you will have to break the areas apart:
=TEXTJOIN(",",true, D36:D63, D66:D68, D71:D72)
assuming you have 365 then you can use TEXTJOIN and even give deliminator like a comma ","
if you don't have access to TEXTJOIN you can use CONCAT(D36:D72 & ","), but I think TEXTJOIN and CONCAT we added at the same time but it is possible a copy of excel 2019 might have concat and not textjoin I suppose.I just noticed you skip a few cells so you will have to break the areas apart:
=TEXTJOIN(",",true, D36:D63, D66:D68, D71:D72)
CONCATENATE did not work, I opened it in 365 to try TEXTJOIN but I am having issues with the function.
you needed the "" around that semi-colon
I take that back, your cells already have the ; so you don't need a deliminator at all
