Data Import & Export

How to Split One Spreadsheet Row into Two Rows Across Multiple Columns

Adam Davis Oct 13, 2026 871 views

Question details

The user needs to separate travel arrival and departure information from a single spreadsheet row into two separate rows by moving specific columns (T through AG) to the second row without using any add-ins.

How to Split One Spreadsheet Row into Two Rows Across Multiple Columns

Product: Spreadsheet
Device & OS: not provided
Scenario: Reorganizing an unformatted travel itinerary where arrival and departure details are merged into a single long row, aiming to split it for better readability.
Observed behavior: The goal is to seamlessly move the data from columns T1 through AG1 into a newly created second row while maintaining the correct data formats, especially for date values.

Before you start

Create a duplicate sheet of your original travel data before attempting to split the rows. This protects your source information from accidental deletion or formatting loss during the transformation process.

Solution 1Recommended

Manually Splitting the Row with Copy and Paste

A straightforward and direct method for splitting a few rows without relying on complex functions or third-party add-ins.

If you only have a few rows of travel data to adjust, manually cutting and pasting the data is the fastest approach. It avoids the need to set up complex formulas.

Insert a blank row

Select the row directly below your target row (e.g., Row 2). Right-click the row number header and choose 'Insert' to create a new blank row.

Select the data to move

Highlight the specific cells you want to move to the new row. For instance, click and drag to select cells T1 through AG1.

Cut the selected cells

Press Ctrl+X on your keyboard, or right-click the highlighted area and select 'Cut'.

Paste into the new row

Click on the first cell of the newly created blank row where you want the data to start (e.g., A2 or T2) and press Ctrl+V to paste the departure data.

Manually Splitting the Row with Copy and Paste

Format Painter Limitation: Remember that the Format Painter tool only copies visual formatting (like colors and borders). It cannot move, split, or rearrange actual cell data.

Solution 2

Using the INDEX Formula to Reorganize Data

Best for automating the split process if you have many rows of travel data that need to be reorganized into two separate rows.

Solution 3

Transforming Rows with Power Query

Ideal for large datasets requiring automated, repeatable data cleaning and unpivoting without manual copy-pasting.

Reorganize Your Data Effortlessly

Easily Split and Manage Spreadsheet Rows with WPS Office

WPS Spreadsheet provides a comprehensive set of data management tools, including advanced formulas, seamless copy-pasting, and powerful data manipulation features to help you split rows and reorganize your travel itineraries with zero hassle.

1. Open your travel spreadsheet: Launch WPS Spreadsheet and open the file containing the merged arrival and departure data.
2. Insert a new row: Right-click the row number header beneath your target data and select 'Insert Row'.
3. Move the required columns: Highlight the cells you wish to move (e.g., columns T to AG), cut them using Ctrl+X, and paste them into the newly inserted row with Ctrl+V.
4. Fix date formatting: If dates appear as raw numbers, highlight them, right-click, select 'Format Cells', and choose 'Date' to instantly restore proper formatting.

Free and lightweight alternative to Microsoft ExcelFully compatible with Microsoft Excel .xlsx and .xls formatsSupports advanced array formulas like INDEX and OFFSET for splitting dataUser-friendly interface for fast manual data entry and formatting adjustments

microsoft office alternative - wps office

Frequently Asked Questions

Why did my date format change to a random number after moving it to a new row?

Spreadsheets store dates as sequential serial numbers for calculation purposes. If the new row is formatted as 'General', dates will appear as their underlying serial numbers (e.g., 44197). To fix this, simply select the affected cells, right-click, choose 'Format Cells,' and select the 'Date' category to restore the proper display.

Can I use the Format Painter to split data into two rows?

No. The Format Painter tool is strictly designed to copy visual formats (such as font size, cell color, borders, and number formatting) from one cell to another. It cannot extract, split, or physically move the actual text or numerical values contained within the cells.

Is there an automatic way to split hundreds of rows without using an add-in?

Yes. For large datasets, you can use Power Query (Data > From Table/Range) to unpivot or append queries, which systematically stacks your columns into separate rows. Alternatively, you can use dynamic formulas utilizing the INDEX and ROW functions to automatically restructure the data into multiple rows without relying on third-party add-ins.

Excel & Data Problems