Data Import & Export

How to Separate Names and Email Addresses into Columns in Excel

Guest Writer Oct 1, 2026 868 views

Question details

The user needs to convert a single-column list where names and email addresses appear on alternating rows into two distinct columns for better data organization.

How to Separate Names and Email Addresses into Excel Columns

Product: Excel
Device & OS: not provided
Scenario: Reformatting unorganized contact lists imported from text files or external systems into a standard tabular database.
Observed behavior: Contact names and their corresponding email addresses are vertically stacked in a single column instead of side-by-side.

Before you start

Ensure your source data consistently alternates between a name and an email address without any blank rows or missing details, as this method relies on a strict alternating pattern.

Solution 1Recommended

Use INDIRECT and ROW Formulas to Extract Data

This method dynamically references alternating rows, automatically pulling names from odd rows and emails from even rows into separate columns.

By combining the INDIRECT and ROW functions, you can grab data at specific intervals. This is the most efficient way to transform vertical alternating lists into a standard two-column table format.

Prepare your destination cells

Click on a blank cell in a new column where you want the 'Names' to appear (for example, cell C1).

Enter the formula for Names

Type the formula =INDIRECT("'Sheet1'!A"&ROW()*2-1) and press Enter. This extracts data from row 1, 3, 5, etc. Be sure to replace 'Sheet1' with the actual name of your data worksheet.

Enter the formula for Email Addresses

Click on the adjacent cell for the email addresses (e.g., cell D1) and type =INDIRECT("'Sheet1'!A"&ROW()*2). Press Enter to extract data from row 2, 4, 6, etc.

Apply formulas to the entire list

Select both formula cells, click and hold the small square at the bottom-right corner (the fill handle), and drag it downwards until all names and email addresses are separated.

Use INDIRECT and ROW Formulas to Extract Data

Working on the same sheet: If you are writing the formula on the exact same worksheet as your source data (assuming source is in Column A), you can omit the sheet reference and simply use =INDIRECT("A"&ROW()*2-1).

Efficient Data Management Tool

Separate Contact Lists Effortlessly in WPS Spreadsheet

WPS Spreadsheet provides seamless support for advanced data manipulation formulas like INDIRECT and ROW, allowing you to instantly reorganize imported contact lists into structured columns.

1. Open WPS Spreadsheet: Launch WPS Office and open your document containing the unorganized contact list.
2. Input the Names Formula: Select a blank column cell and enter =INDIRECT("A"&ROW()*2-1) to extract the alternating names.
3. Input the Email Formula: In the adjacent cell, enter =INDIRECT("A"&ROW()*2) to extract the email addresses.
4. Drag to Autofill: Select both cells and drag the fill handle down to convert the entire column.

100% compatible with Microsoft Excel formulas and formattingFree, lightweight, and fast installationBuilt-in data cleaning tools for quick list organization

Frequently Asked Questions

What if my data has blank rows between the names and emails?

The INDIRECT formula method requires a strict alternating pattern. If you have blank rows, you should first remove them. Select your data column, press F5 (Go To), click 'Special', select 'Blanks', right-click a highlighted blank cell, and choose 'Delete'.

How do I remove the formulas and keep just the text?

After your data is separated, highlight the new 'Names' and 'Emails' columns, copy them by pressing Ctrl+C, right-click on the same selected area, and choose 'Paste as Values' (the clipboard icon with a 123 on it).

Can I use Text to Columns instead of formulas?

The 'Text to Columns' feature under the Data tab is useful only if the name and email are in the exact same cell separated by a delimiter (like a comma or space). If they are on completely different rows, you must use formulas or Power Query.

Excel & Data Problems