parsing the text

[matej papez]

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.

[#2]

Hi Matej,

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

[#3]

Hi Haytham,

no problem. I have windows too. Can you show me how to do it?
Thank you.

[#4]

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 Power Query add-in.

Update:

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

[#5]

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

[#6]

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.

[#7]

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.

[#8]

PERFECT!!

Thank you!!

[#9]

Hi Mr Amairah,

I have a problem to sort multiple text in one cell into multiple cell.

Please see my attachment on my raw data and my wanted data to be.

Could you teach me how to change into that wanted data.

Really appreciate your assistance.

Thank you.

[#10]

Hi @fazly858,

Sorry, but I can't figure out a suitable solution to convert that raw data to what you want.

I suggest you re-post this request as a new post in the community.

Regards