parsing the text

Question

Hi,&nbsp;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.&nbsp;Please see attached picture.

matej papez · Answer

Hi Haytam,&nbsp;this works great but only if I have text value. If I have only one number in cell i get an error. Why?&nbsp;Please see attached picture.

haytham amairah · Answer

Hi Matej,&nbsp;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.&nbsp;Anyway, I've updated the query to take into account this issue and convert all the records to texts from the beginning.

haytham amairah · Answer

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

matej papez · Answer

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

haytham amairah · Answer

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!

Forum Discussion

parsing the text

Share

Resources