Split Multiline Cells

Question

&nbsp;I am using excel 2019 and i have a worksheet that has 4 columnsEmpID&nbsp; &nbsp; &nbsp; &nbsp; Pay Code&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Hrs&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AmountXYZ&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Hourly&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; OT&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;300&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Vac&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 40&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;800&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Sick&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;20&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;400The info is in a Single Row. Each cell is MultiLine and I need to split it. Basically I want to convert it in to 4 rows where each pay code and related hours and amounts are on separate row

nikolinode · Answer

nfpacct&nbsp;To split the multiline cells in Excel and convert them into separate rows, you can use the following steps:Select the range of cells containing the multiline data that you want to split (in your case, columns B, C, and D).Copy the selected range by pressing Ctrl+C.Right-click on an empty cell in your worksheet where you want to paste the split data, and select "Paste Special" from the context menu.In the Paste Special dialog box, choose the "Transpose" option. This will transpose the copied data from columns to rows.Click "OK" to paste the transposed data.Now, in the newly pasted data, select the range that includes the Pay Codes, Hours, and Amounts.Go to the "Data" tab in the Excel ribbon and click on the "Text to Columns" button.In the Text to Columns wizard, select the "Delimited" option and click "Next".In the next step, choose the delimiter that separates the Pay Codes, Hours, and Amounts in your data (in this case, it appears to be spaces or tabs).Preview the data in the Data preview section to ensure it is splitting correctly. Adjust the delimiter settings if needed.Click "Finish" to complete the process. The multiline data will now be split into separate rows with the respective Pay Codes, Hours, and Amounts.You can now delete the original row that contained the multiline data, if desired, as you now have the split data in separate rows.Note: This process assumes that the EmpID remains the same for each pay code and is not split across multiple lines. If the EmpID is also multiline, you may need to use additional steps to split that as well. The text and steps were created with the help of AI.My answers are voluntary and without guarantee!&nbsp;I hope this helps!

nfpacct · Answer

NikolinoDE&nbsp;Thanks - but unfortunately it did not work. When transpose it all three columns are transposed in to a single column which is not the desired result. If I were to use the text to columns it will not give me the desired result.&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Split Multiline Cells

Share

Resources