Recently I had a need to export content from one system and import into another.
The exported data included a cell with multiple values delimited/separated with an underscore (“value1_value2_value3”).
In the importing system I needed separate rows for each of the values in that cell, but with all other cells in other columns of that row to simply duplicate in the new rows .
Apparently this is simply achievable using excel’s power query.
Here are the steps:
- Select any cell in your datasheet and press Ctrl+T to convert all data to a table
- Top menu – click “Data”->”From Table/Range”
the Power Query Editor opens - In the table – select the column you wish to split and click “Split Column”->”By Delimiter”
- Select the delimiter used in your data
- Click “Advanced options”->”Split into”->”Rows”
- Click “OK”. The table data will preview the changes
- Click “Close and Load”
A new worksheet is created with the processed data
Good luck and let me know if this helped you.