Forum Discussion

anupambit1797's avatar
anupambit1797
Iron Contributor
Oct 02, 2024

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  
BI & Data Analysis
excel
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Oct 02, 2024

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.

 

  • anupambit1797's avatar
    anupambit1797
    Iron Contributor
    Oct 02, 2024

    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

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Oct 02, 2024

      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)],",")
    )
  )

       

       

    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor
      Oct 03, 2024

      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.

       

Resources