Replace "," with a "Newline" in Excel

[anupambit1797]

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

[#2 · Accepted solution]

Hi @anupambit1797 

With Power Query Split Column (on the Home tab)

[#3]

@anupambit1797 

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.

[#4]

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

[#5]

@anupambit1797 

Replace YourTableName belows with your actual Table name:

= TOCOL(
    HSTACK(
      TEXTBEFORE(YourTableName[RLC-NR_Sequence Numbers(Subcell-9)],","),
      TEXTAFTER(YourTableName[RLC-NR_Sequence Numbers(Subcell-9)],",")
    )
  )

[#6]

Thanks a lot@Lorenzo ,

Br,

Anupam

[#7]

@anupambit1797 

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.