Excel’s Text to Columns feature splits text across columns, not down rows. To make the split results vertical, split the text first and then transpose the result, or use a formula that splits directly into rows.
What Text to Columns does
In Excel for Microsoft 365 on Windows, Text to Columns takes text in a column and separates it into neighboring columns. For example, splitting the value red,blue,green at each comma produces three cells on the same row: red, blue, and green. The command does not turn one cell into a vertical list.
The feature is useful when a cell contains several fields, such as a first and last name separated by a space, or city and state separated by a comma. It can split at a delimiter you choose or at fixed character positions. The direction matters: Text to Columns works across each source row. If your goal is one item per row, you need an additional step or a different method.
“Vertical” can also mean two different things: turning one cell’s separated items into a list, or rotating an existing horizontal range so its values run down a column. The distinction helps you choose the right approach.
Split text with the Text to Columns wizard
Use the built-in wizard when you want to separate values into columns before rotating them, or when you need to process several source rows in one operation.
- Make a copy of the source column or check that the cells to its right are empty. Text to Columns can overwrite existing content in its output area.
- Select the cells containing the text you want to split.
- Choose Data > Text to Columns.
- Choose Delimited if a character such as a comma, tab, semicolon, or space separates the values. Choose Fixed width if the fields begin at consistent character positions.
- Set the delimiter or break positions in the wizard, review the preview, and select Next.
- In the final step, set a destination if you do not want the output to begin in the original cells. Set a column’s format to Text when you need to preserve entries such as postal codes or product IDs with leading zeros. Select Finish.
For example, splitting Jordan Lee at the space creates Jordan and Lee in separate columns. If the selected source cells contain multiple rows, Excel splits each row separately; it does not combine all the results into one vertical list.
Transpose the split results into a column
If Text to Columns has put the desired items across one row, transpose that range to rotate its rows and columns. Suppose the split results are in B2:D2: red, blue, and green. Copy the cells, select an empty destination cell, and choose Home > Paste > Transpose. The three values will be pasted down the destination column.
Check the destination before pasting. It needs enough empty cells below it, and the pasted range must not overlap data you want to keep. After confirming the transposed values are correct, you can remove the temporary horizontal results if appropriate. If you need a live result that updates when the source changes, use a formula instead of a one-time paste.
Transposing a two-dimensional range rotates the entire layout. For example, a range that is three columns wide and two rows tall becomes two columns wide and three rows tall. It does not automatically stack every value from a multirow, multicolumn range into one long column. If you need a single list from many source rows, Power Query or a purpose-built formula may be more suitable.
Split directly into rows with a formula
In Microsoft 365 or Excel 2024, the TEXTSPLIT function can use a row delimiter to place each item on its own row without running the Text to Columns wizard. This is convenient when the source is one text string in one cell.
With North|South|West in A2, enter this formula in an empty cell:
=TEXTSPLIT(A2,,"|")
The second argument is left empty because no column delimiter is needed; the third argument identifies the row delimiter. The formula returns North, South, and West vertically in three cells. The result is a dynamic array, so Excel fills the cells below the formula automatically. Leave that spill area empty or Excel may show a #SPILL! error.
If you already used Text to Columns and the results are in B2:D2, you can rotate them with a formula. With those three values in B2:D2, enter the following in an empty cell:
=TRANSPOSE(B2:D2)
The result is the same three values in a vertical range. In current Microsoft 365, the result spills automatically. In older Excel versions without dynamic arrays, select the destination cells first and confirm the formula as an array formula if that version requires it. TEXTSPLIT is available in Microsoft 365 and Excel 2024, while TRANSPOSE is also available in earlier Excel versions.
Choose a method for your data
The right method depends on how the text is arranged and whether the result should update when the source changes:
- One cell, one vertical list: Use
TEXTSPLITwith a row delimiter in Microsoft 365 or Excel 2024. - Text to Columns output is already split across one row: Use Home > Paste > Transpose for a one-time result, or
TRANSPOSEfor a formula-driven result. - Many source rows with several delimited items in each: Consider Power Query’s split-into-rows option. Transposing each row individually will create separate vertical lists rather than one combined list.
- Fields have predictable character positions: Text to Columns with Fixed width may be more reliable than choosing a delimiter.
For example, if A2 contains red,blue and A3 contains green,yellow, using Text to Columns creates two results on row 2 and two more on row 3. Transposing just the first row will not produce one list containing all four values. Decide whether you want to preserve each source row’s grouping or combine every split item before choosing a method.
Use Power Query for multiple rows
Power Query is useful when many cells contain delimited lists and you want each item to become a separate row in one output column. In Excel for Microsoft 365 on Windows, put the source data in a table, click inside it, and choose Data > From Table/Range. In the Power Query editor, select the column to split, then choose Split Column > By Delimiter.
Choose the delimiter, open Advanced options, and select Split into Rows rather than splitting into columns. Confirm the operation, then choose Home > Close & Load to return the transformed data to Excel. The exact editor layout can vary slightly with updates, but the split direction is the important setting.
Power Query creates a repeatable transformation rather than a formula in every cell. If the source table changes, refresh the query to update its output. This is particularly helpful for recurring files, but it is more setup than needed for a one-time split. Review the result before replacing or deleting original data, and remember that refreshes can change the output range.
Common problems and how to avoid them
- Text to Columns overwrites neighboring cells: Choose a safe destination in the wizard or move the source to a clear area first. Keep a backup when the original values matter.
- Leading zeros disappear: Excel may interpret split values as numbers. In the wizard’s final step, select the affected output column and set its format to Text before finishing.
- Dates change format: Excel can interpret date-like text as dates during splitting. Review the preview and choose an appropriate column format in the final wizard step if the original text must be preserved.
- Some values remain unsplit: Check for inconsistent delimiters, such as a comma followed by a space in some cells but not others. A delimiter must match the actual characters in the source.
- A formula returns
#SPILL!: Clear cells in the intended output area, including cells that look empty but contain spaces or formulas. Dynamic-array results need unobstructed cells. - Empty items disappear or appear unexpectedly: Consecutive delimiters or a delimiter at the start or end of text can create empty fields. Inspect the source and test the result on representative rows before processing a large range.
Also note that Text to Columns changes how the selected text is stored. If the input contains values that look numeric, Excel may treat them as numbers after splitting. When exact text matters—such as an identifier, an account code, or a code beginning with zero—choose Text formatting or use a method that preserves the original characters.
Frequently Asked Questions
Can Text to Columns split text vertically by itself?
No. Text to Columns separates values into columns. To put those results in rows, transpose the output or use TEXTSPLIT with a row delimiter in Microsoft 365 or Excel 2024.
How do I turn a comma-separated cell into a vertical list?
If the text is in A2, enter =TEXTSPLIT(A2,,",") in an empty cell in Microsoft 365 or Excel 2024. Excel spills each comma-separated item into a separate row below the formula.
How do I transpose columns into rows in Excel?
Copy the range, select an empty destination, and choose Home > Paste > Transpose. For a result that updates with its source, use the TRANSPOSE function instead.
Can I split multiple cells into one vertical list?
Yes, but Text to Columns followed by transposing one row at a time may not combine the results the way you expect. Power Query’s Split into Rows option is a practical choice for repeating transformations across many records.
Does Text to Columns work the same way on Mac?
The core behavior is the same: it splits text into columns rather than rows. Excel for Mac may place commands differently depending on the version, so look for Text to Columns on the Data tab. Use the transpose paste command or a formula to make the results vertical.
Related
- Excel Remove Duplicates: Why No Duplicates Are Found and What to Check
- Excel Remove Duplicates: Which Rows Stay and Which Go?
- Excel Remove Duplicates by Multiple Criteria
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcut to Select to the Bottom of a ColumnIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+Down Arrow to extend a selection from the active cell…
- Add an Auto Reply to a Shared Mailbox in Outlook 365To add an automatic reply to a shared mailbox, open that mailbox in Outlook on the web and…
- Wrap Text in Excel: Keyboard Shortcut and Quick StepsIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, then H, then…
- Corrupted Excel File Repair: What to Try FirstFor a corrupted Excel file, first make a copy of the file, then try Excel’s built-in Open and…
- Excel Remove Duplicate Rows Based on One Column SafelyTo remove duplicate rows based on one column in Excel for Microsoft 365 on Windows, select a cell…
- Data Bar Conditional Formatting in Excel: Setup and TipsIn Excel for Microsoft 365 on Windows, select the numeric cells, then choose Home > Conditional Formatting >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.