Convert Vertical Excel Data to First Name, Last Name, Phone, and Email Columns
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.

- 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.
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.
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.
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.
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)))
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.
Use the CHOOSEROWS Sequence Method
An alternative dynamic array approach utilizing CHOOSEROWS and SEQUENCE to selectively pull data into the correct columns.
Use Power Query (Legacy Excel Versions)
For older versions of Excel (like Excel 2013 or 2016) that do not support dynamic array functions, Power Query is the most reliable method.
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. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the vertical dataset.
- 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. Instantly Format: Press Enter to instantly transform your vertical records into a structured, fully separated four-column horizontal table.

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.




