Data Import & Export

Convert Vertical Excel Data to First Name, Last Name, Phone, and Email Columns

Olivia MillerOlivia Miller Nov 10, 2026 868 views

Question details

Restructure repeating three-row vertical records into a structured four-column table containing separated first and last names, phone numbers, and email addresses.

How to Convert Vertical Excel Data into Multiple Columns
Product
Excel
Device & OS
not provided
Scenario
A user has a dataset where contact information (Name, Phone, Email) is listed vertically in a single column in repeating blocks of three rows. They need to transpose and separate this data into distinct columns for First Name, Last Name, Phone, and Email.
Observed behavior
The data is currently arranged vertically in a single column, making analysis and sorting impossible. The desired state is a fully organized, horizontal table layout with distinct columns.
Before you start

Ensure your vertical data follows a strict repeating pattern (e.g., exactly three rows per record) with no missing rows or blank cells, as the formulas rely on mathematical consistency to wrap the data correctly.

Solution 1Recommended

Use Dynamic Array Formulas (Modern Excel)

This is the most efficient method for modern spreadsheet software, utilizing the WRAPROWS, LET, and HSTACK functions to instantly format the vertical data and split the names.

This solution utilizes a combination of modern array functions. WRAPROWS converts the single column into multiple columns. The LET function allows us to define variables to split the full name into a first name and last name using TEXTBEFORE and TEXTAFTER, then HSTACK combines it all together.

1
Select the destination cell

Click on the top-left cell of the blank area where you want your new four-column table to begin. Ensure there is enough empty space to the right and below for the data to spill.

2
Enter the comprehensive LET formula

Type the following formula, replacing A1:A12 with your actual vertical data range: =LET(wrapArray,WRAPROWS(A1:A12,3),fullNames,TAKE(wrapArray,,1),HSTACK(TEXTBEFORE(fullNames," ",,,1),TEXTAFTER(fullNames," ",,,1),DROP(wrapArray,,1)))

3
Execute the array formula

Press Enter. The formula will automatically spill the results, converting every three source rows into one horizontal record, isolating the first name and last name, and populating the phone and email columns.

Spill Range: Because this is a dynamic array formula, you only need to enter it into the top-left cell. It will automatically populate the remaining cells needed.

Easily Transform Complex Data with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas and provides powerful data processing tools to help you manage complex data restructuring with ease. Try it as a lightweight and highly compatible alternative.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the vertical dataset.
  2. 2. Apply the Formula: Select a blank cell and paste the dynamic array formula (e.g., the WRAPROWS and HSTACK combination) exactly as you would in other modern spreadsheet software.
  3. 3. Instantly Format: Press Enter to instantly transform your vertical records into a structured, fully separated four-column horizontal table.
Free and lightweight office suite with rapid startup speedsSeamless compatibility with Microsoft Excel (.xlsx) formats and legacy filesFull support for modern Excel dynamic array functions like LET, WRAPROWS, and HSTACKFamiliar user interface requiring zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

What if my vertical data is missing a row for some contacts?

Dynamic array functions like WRAPROWS expect a strict repeating pattern (e.g., exactly 3 rows per contact). If data is missing, the transformation will misalign. You must clean your source data by inserting blank placeholder cells for any missing information before applying the formula.

Why am I getting a #NAME? error when using the WRAPROWS formula?

The #NAME? error indicates your spreadsheet software does not support modern dynamic array functions. If you are using legacy versions like Excel 2013 or 2016, you should use the Power Query method instead.

Can I use the Transpose feature instead of complex formulas?

Yes, you can copy a single vertical block of data, right-click a destination cell, and choose 'Paste Special > Transpose'. However, this is a manual process that must be done one record at a time, making it highly inefficient for large datasets.

How do I split the full name into first and last name if there are middle names?

The TEXTBEFORE and TEXTAFTER formulas in the provided example split the name at the first space. If a middle name exists, the last name column will contain both the middle and last name. You can adjust the instance number in the TEXTAFTER parameters or use the 'Text to Columns' tool on the resulting data to further refine it.