Forum Discussion

matej papez's avatar
matej papez
Copper Contributor
Apr 14, 2018

parsing the text

Hi,   I am interested in how to convert text that is grouped in one cell (with line break "alt+return") into each cell individually. And vice versa.   Please see attached picture.
excel
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
Apr 14, 2018

Hi Matej,

 

This can easily be done by using Get & Transform (Power Query).
But unfortunately, it's not supported on the Mac!

matej papez's avatar
matej papez
Copper Contributor
Apr 14, 2018
Hi Haytham,

no problem. I have windows too. Can you show me how to do it?
Thank you.
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 14, 2018

    OK, please find the attached file to test the solution.

     

    You can update the row data and then refresh the next table (Right Click >> Refresh) to see how it works.
    This table is linked to a query that works behind the scenes and it converts the data to the shape you want!

     

    NOTE: If you don't have Excel 2016, you have to download and install the https://www.microsoft.com/en-us/download/details.aspx?id=39379.

     

    Update:

    I've updated the query in the workbook, looks like it was buggy!

    • matej papez's avatar
      matej papez
      Copper Contributor
      Apr 16, 2018

      Hi Haytam,

       

      this works great but only if I have text value. If I have only one number in cell i get an error. Why?

       

      Please see attached picture.

      • Haytham Amairah's avatar
        Haytham Amairah
        Silver Contributor
        Apr 16, 2018

        Hi Matej,

         

        Sorry about that!

        This is because of the conflict between data types.

        The records in cells A5 and A6 are actual numbers but the rest are texts because they contain some line breaks and forward slashes.

         

        Anyway, I've updated the query to take into account this issue and convert all the records to texts from the beginning.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 14, 2018

      As a comment, better to split by line break delimiter

      let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SplitByLF = Table.SplitColumn(Source, "Letters",
        Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Letters.1", "Letters.2", "Letters.3"}),
    Unpivot = Table.UnpivotOtherColumns(SplitByLF, {}, "Attribute", "Letters"),
    Remove = Table.RemoveColumns(Unpivot,{"Attribute"})
in
    Remove

       

Resources