Data Import & Export

How to Stop Excel from Splitting One Cell into Multiple Rows When Pasting

Phi Hung Vo Oct 7, 2026 868 views

Question details

The user needs to prevent data from splitting into multiple rows when pasting cells that contain line breaks into a new Excel worksheet.

How to Prevent Excel from Splitting One Cell into Multiple Rows When Pasting

Product: Excel
Device & OS: not provided
Scenario: Pasting a PivotTable or a large dataset (over 10,000 records) with embedded line breaks into a new worksheet.
Observed behavior: Excel creates multiple rows for a single cell's data because it interprets the embedded line breaks as row separators.

Before you start

Ensure you have Microsoft Word or WPS Writer available to perform a temporary find-and-replace operation on the copied data before pasting it into your spreadsheet.

Solution 1Recommended

Use Word to Replace Line Breaks Before Pasting

Copy the data into a word processor to safely remove and temporarily replace line breaks, preventing Excel from splitting the cells.

Because Excel natively treats line break characters as instructions to start a new row when pasting, using a word processor to temporarily mask these characters is the most reliable workaround for large datasets.

Copy the Data to Word

Select the PivotTable or dataset containing the line breaks, copy it using Ctrl+C, and paste it into a blank Microsoft Word or WPS Writer document.

Replace Line Breaks with a Temporary Character

Press Ctrl+H to open the Find and Replace dialog. In the 'Find what' box, type '^p' (for paragraph marks) or '^l' (for manual line breaks). In the 'Replace with' box, type a unique temporary character string like '###'. Click 'Replace All'.

Paste the Cleaned Data into Excel

Select all the updated text in your document, press Ctrl+C to copy it, and paste it into your new Excel worksheet. All data for each record will now correctly remain in a single row.

Restore the Line Breaks in Excel

Select the newly pasted data in Excel and press Ctrl+H. Enter your temporary string ('###') in the 'Find what' box. In the 'Replace with' box, press Ctrl+J to insert a line break command. Click 'Replace All'.

Use Word to Replace Line Breaks Before Pasting

Large Datasets Supported: This method works efficiently even for large datasets containing over 10,000 records, ensuring each buyer's data stays precisely on its corresponding row without manual editing.

Manage Large Datasets Flawlessly with WPS Office

Avoid pasting errors and handle large datasets with ease using WPS Office. WPS Writer and WPS Spreadsheet offer seamless integration, allowing you to use advanced Find and Replace functions to clean your data and preserve cell formatting without splitting rows.

1. Open WPS Writer and WPS Spreadsheet: Launch WPS Office and open a new blank Writer document as well as your target Spreadsheet.
2. Clean the Text in WPS Writer: Paste your original data into WPS Writer. Press Ctrl+H, enter '^p' or '^l' in the Find box and a placeholder like '###' in the Replace box, then click Replace All.
3. Transfer and Restore in WPS Spreadsheet: Copy the cleaned text and paste it into WPS Spreadsheet. Press Ctrl+H, enter '###' in Find, click inside the Replace box and press Ctrl+J, then hit Replace All to restore the line breaks perfectly.

Advanced Find and Replace tools work seamlessly across documents and spreadsheets.Flawlessly handles heavy operations on datasets with over 10,000 records.High compatibility with Microsoft Excel (.xlsx) and Word (.docx) formats.Free, lightweight, and user-friendly suite for daily productivity.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel split one cell into multiple rows when pasting?

When you copy data containing embedded line breaks (such as Alt+Enter formatting) and paste it into a new worksheet, the clipboard often passes the line break character to Excel. Excel interprets this character as a command to jump to a new row, forcing the remainder of the cell's contents into the cell directly below it.

What does Ctrl+J do in Excel Find and Replace?

Ctrl+J inserts a carriage return (line break) command directly into the Find and Replace dialog box. While you won't see a standard character, a small blinking dot might appear in the text box, indicating that Excel is ready to search for or replace text with an embedded line break.

Can I use formulas to fix cells that split into multiple rows?

Yes, you can use formulas like CLEAN() or SUBSTITUTE(A1, CHAR(10), " ") to remove non-printable characters including line breaks before copying the data. However, if your goal is to retain the actual line breaks within the cell after moving the data, using the Find and Replace method with a temporary placeholder is the most effective approach.

Excel & Data Problems