The other day, I stumbled upon the same old issue almost a decade after this topic was started. The proposed solutions here required additional steps, adding complexity that I wanted to avoid. So I found an alternative way that worked for me.
I needed to process some data in Excel and output a few numeric values integrated into XML code.
My Core Idea
Processed data (simplifed)
Item 1
Item 2
Desired output (simplified)
<li>Item 1</li>
<li>Item 2</li>
Initially, I tried to generate the output as multiline text in a single cell using CHAR(10). The text copied to my XML file looked like this
"<li>Item 1</li>
<li>Item 2</li>"
Then I came to understand that I simply need to create each line of code in a separate cell and copy the lines.
each line of code in a separate cell
That solved the issue for me.
And when you think of it, why create a multiline text in a single cell if you intend to insert it in some other software? If, for any reason, you also need to have it in a single cell in Excel, you can just create the text in multiple cells in another worksheet and concatenate it in your worksheet using CHAR(10) or CHAR(13).
My Actual Issue
I used Excel to calculate coordinates for some XML structures, then I needed to integrate the coordinates into the pre-defined lines of XML code.
svg:x="[coor_x]cm"
svg:y="[coor_y]cm"
Since I had many pairs of coordinates, it seemed natural to generate multiline text for each pair of coordinates so that I can drag the formula down easily.
each cell contains two lines for coor_x and coor_y
But the output inserted in a different software was less than desirable:
"svg:x=""1.43cm""
svg:y=""2.47cm"""
"svg:x=""0.53cm""
svg:y=""2.12cm"""
Another approach was to have a formula for coor_x in even rows and a different formula for coor_y in odd rows. But then I would lose the ability to drag formulas down as many rows as I need. I would need to copy and paste this pair of formulas below a lot of times and re-adjust wrong references to cells storing coordinates.
pair of formulas in two rows that need to be copied multiple times
Finally, once I found that my version of Excel supported dynamic arrays, it dawned on me how to do it properly. (I believe it should also work on older versions of Excel using named ranges.)
I generated all lines for coor_x in one column, then generated all lines for coor_y in another column, then used a modified version of the formula to stack data from the two columns into a single column:
=LET(a,TRANSPOSE(A8:B9),r,ROWS(a),seq,SEQUENCE(r*COLUMNS(a),,0),arr,INDEX(a,MOD(seq,r)+1,seq/r+1),arr)
generate all lines for coor_x in one column, generate all lines for coor_y in another column, use the formula below to stack data from multiple columns into a single column
Using dynamic arrays in creative ways, you can even do away with the need to adjust array regions manually when you add more pairs of coordinates.
Hope this solution will also be helpful!