Forum Discussion
Solved
Replace "," with a "Newline" in Excel
Dear Experts,
I have a query like below ;-
In Column "A", I have data like below, and need Output like in Column "D" ;
Thanks in Advance,
Br,
Anupam
Staying within the Excel formula environment, you could have
= TOCOL( HSTACK( TEXTBEFORE(pairs,","), TEXTAFTER(pairs,",") ) )The more obvious use of TEXTSPLIT that would have mirrored the PowerQuery operation more closely fails because of Microsoft's well-known 'array of arrays' implementation error.
Thanks , PeterBartholomew1 could you please help to provide the result sheet with the formula,
I tried the one you suggested but, seem to throw me a #NUM err.
Thanks in Advance,
Br,
Anupam
Seems I did not hit Save on the file. Still, it was quick enough to reintroduce the defined name 'pairs' and copy/paste the formula.
Stacking variable length comma-separated strings is something that is possible, but far harder to achieve.
