Excel Breaking Cell Value into Multiple Rows

Question

After copying a number of rows with cells having multiple lines from email, other sources, or same Excel file and pasting into another Excel file or Excel web application, the data break into multiple rows. For example, 2 rows have a column where a cell contains 3 lines of data, the number of rows became 6 when pasted into Excel. The expected result is that, if there are 3 lines of data from the original cell of 1 row, that data should still be in a single cell and should still be 1 row; not 3 rows (please refer to screenshots). How to resolve this? Thanks in advance.

Below is the expected output which was manually done by using F2 on a single cell then cut-pasting other data to be similar to the original table; but if there are a thousand rows of these, it will be a time-consuming effort. Is there a way to copy-paste the table and the number of rows are still the same like the original table from other documents?

Answer 1

Got your problem.

You can achieve this task using two methods.
1st - Using help of google sheets

• First open a blank google sheet by typing "google sheets" on google • Paste your table into google sheets you can see the formats would be same as previous
•Now you need to copy the same from google sheet and paste this into Excel sheet.

2nd- Using help of Word

• Copy the table from source and paste this into Word
• Here you need to replace the line breaks with some unique character
• Now copy paste the table into Excel and revert back the previous reppacement which was done in word

you can find more details about 2nd method here;

https://answers.microsoft.com/en-us/office/foru...

Please reply us back if you still have any questions or mark this post as resolved with your feedback :)

Thanks,
Ali

Answer 2

Hi Sir,
Could you please elaborate what issue you are facing after following the steps shared?

Also sharing the screenshot would be helpful to understand.

Thanks,
Ali

Answer 3

Hi Ali, please see screenshots below. Original table contain only 2 rows; but after copying it and pasting into Excel, they became 6 rows. I am expecting to still have 2 rows where the 3 lines of data in TestData column should still be in 1 single cell. In the screenshot (on the right), Row# 1 & 2 were merged; but they are actually 6 rows (range A2 - A7). I am expecting to have only Range A2 - A3 and the data in TestData column will only be on Cell B2 & B3.

Below is the expected output which was manually done by doing F2 then copy-pasting each row of TestData into a single cell; but if I have a thousand rows of these, it will be a time-consuming effort. Is there a setting to automatically copy the exact number of rows from the original table of other document?

Answer 4

Thanks. It solved the issue; but I think it is just a workaround and would be better if Microsoft Excel will be enhanced to have the same functionality without having to transfer from one application to another.