Forum Discussion
Taking text from a multi-line cell and making each line its own row.
Hello,
I have a sheet with several thousand lines that I pulled from my current system, and all of the data was exported into cells like this:
What I would like to do is take each line and make that line its own row in the sheet. For example, the scooter would have its own row, the MWC have its own row, so on and so forth. Is there any way this is possible, or should I be looking to see if there is any other way I can export this data?
2 Replies
Sélectionne la colonne contenant les données
Va dans Données puis Convertir
Choisis Délimitation puis clique Suivant
Coche la case du séparateur utilisé
Suite, sélectionne les nouvelles colonnes et utilise une transposition verticale
Copie les cellules
Clique dans une cellule vide
Clique droit puis Collage spécial puis Transposeur.
With legacy Excel such as Excel 2013 you can use Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.
The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.
=DROP(REDUCE("",L2:L6,LAMBDA(u,v,VSTACK(u,TEXTSPLIT(v,,CHAR(10))))),1)With Microsoft 365 or Excel online you can apply this formula.
