Can You Do Text to Columns Vertically in Excel?

Not directly: Excel’s Text to Columns command splits text across separate columns, not down separate rows. To split one cell into a vertical list in Microsoft 365, use TEXTSPLIT with the separator in its row-delimiter argument; for a one-time result, you can also split horizontally and transpose it.

What “Text to Columns” Does

Text to Columns is Excel’s built-in tool for dividing text in a cell or column into fields. For example, it can turn Alex Morgan,Seattle,WA into three neighboring cells: Alex Morgan, Seattle, and WA. The results run horizontally across the worksheet, which is why the command is called Text to Columns.

In Excel for Microsoft 365 on Windows, select the source cells and choose Data > Text to Columns. The wizard lets you split by a delimiter, such as a comma, tab, or space, or by fixed-width positions. There is no setting in that wizard that changes the output direction to rows. If you want each separated item to appear beneath the previous one, use a different method.

It helps to distinguish two tasks that people may mean by “vertically.” You might want to split the contents of one cell into multiple rows, or you might want to turn a horizontal group of already-split cells into a vertical group. The first is a split operation; the second is a transpose. Choosing the right one avoids an unnecessary extra step.

Split One Cell Into Rows With TEXTSPLIT

For a formula-driven vertical split, TEXTSPLIT can use a row delimiter. Its second argument is the column delimiter and its third is the row delimiter. Leave the second argument empty when you do not want a column split, then put the separator between the items in the third argument.

Assume the text red,blue,green is in A2. Enter this formula in B2:

=TEXTSPLIT(A2,,",")

The results spill down from B2 as red, blue, and green, one item per row. This function is available in Microsoft 365 and Excel 2024. It is a good choice when the source text may change, because the spilled results update when A2 changes.

For a line-break-separated cell, where A2 contains several lines of text, use:

=TEXTSPLIT(A2,,CHAR(10))

Here, the cell layout is still the source text in A2 and the formula in B2; the result is one line per row. If your data is separated by semicolons instead, replace the comma delimiter in the first example with a semicolon. TEXTSPLIT matches the delimiter you provide, so a space-separated list will not be split by a comma formula.

To avoid empty results when delimiters repeat, such as red,,green, add TRUE as the fourth argument. With the same A2-to-B2 layout, use =TEXTSPLIT(A2,,",",TRUE). This tells Excel to ignore empty items. If empty positions carry meaning in your data, leave this option out so the gaps are retained.

Use Power Query to Split a Column Into Rows

Power Query is useful when you need to split many records, repeat the process after refreshing data, or keep other columns alongside each split item. It can split a delimited value into rows rather than columns, and it can repeat the other fields from the original record for each resulting row.

Make sure the source data has column headings, then select a cell in the data and choose Data > From Table/Range. Confirm the table range if Excel asks.
In the Power Query Editor, select the column to split and choose Transform > Split Column > By Delimiter.
Choose the delimiter. Open Advanced options and select Split into Rows, then confirm.
Review the preview, then choose Home > Close & Load to return the transformed results to Excel.

For example, a table row containing the customer name “Ari” and the tags new,priority,renewal can become three rows, each carrying the customer name and one tag. This is often more useful than a formula when preparing a clean, refreshable dataset for analysis. Power Query’s location and wording can vary slightly across Excel builds, but the split direction is selected in the split dialog’s advanced options.

Split Horizontally, Then Transpose

If you only need a one-time result and prefer the Text to Columns wizard, you can first let it create columns and then transpose those cells into rows. This approach works in Excel versions without TEXTSPLIT, although it takes more manual steps and does not automatically update if the original text changes.

Use Data > Text to Columns to split the source text across columns.
Select the resulting cells in that row and copy them.
Select a blank destination cell, then choose Home > Paste > Transpose.

For example, if Text to Columns puts the words from A2 into B2:D2, copying B2:D2 and pasting with Transpose puts those words into three cells down a column. The original split cells remain in place unless you remove them. Transpose changes the orientation of the copied range; it does not make the original Text to Columns operation vertical.

On Mac, the ribbon may be arranged differently, but Excel also provides a Transpose paste option. If you do not see it on the Home tab, look in the Paste options or Paste Special dialog. In Excel for the web, available paste options can depend on the browser and current selection; a formula or Power Query may be more convenient for repeatable work.

Choose the Method That Fits the Job

The best option depends on whether you need a live result, a repeatable data-cleaning process, or simply a quick reorientation. Consider these differences:

Use TEXTSPLIT for a formula that updates with its source cell and spills into rows automatically. It is quick for a single cell or a small number of cells, but requires a supported Excel version and clear space for the results.
Use Power Query for larger tables, repeated imports, and workflows where you want to refresh the transformation. It is especially helpful when each split value must stay connected to other fields from the same record.
Use Text to Columns plus Transpose for a quick, one-off conversion or when using an older Excel version without TEXTSPLIT. The result is a pasted copy, so you must repeat the steps if the source changes.

These methods are not interchangeable in every respect. A formula is dynamic, while a transpose paste is typically a snapshot. Power Query creates a transformation that can be refreshed, but its output is loaded as query results rather than maintained as a direct cell formula. Think about how the data will be used after splitting, not just how to make it fit vertically.

Prepare the Data and Destination

Before splitting, confirm that the separator has a consistent meaning. A comma works well if commas always divide items, but it can give the wrong result when commas also appear inside a name or description. For example, splitting Seattle, WA, Portland, OR at every comma produces four pieces, not two city-and-state pairs. You may need a more reliable source format, a different delimiter, or a more carefully designed transformation.

Also check for leading or trailing spaces. Splitting red, blue may leave a space at the start of the second item. If clean values matter, remove unwanted spaces after the split, or clean the source before splitting. Do not assume a delimiter operation automatically standardizes capitalization, spelling, or whitespace.

For formula results, leave enough empty cells below the formula for the items to spill. If anything occupies a required result cell, Excel may show a #SPILL! error. Make sure the output will not overwrite important information, and avoid placing a spill formula inside an Excel Table if the desired result needs to expand dynamically; spilled array formulas are not supported within table cells in the same way as in a normal worksheet range.

Common Problems and Misconceptions

A frequent misconception is that choosing a different delimiter or wizard option can make Text to Columns send its output down rows. It cannot: its output is arranged in columns. To get rows, use TEXTSPLIT’s row delimiter, Power Query’s split-into-rows setting, or transpose the horizontal result.

If TEXTSPLIT is not recognized, the Excel version may not include it. The function is available in Microsoft 365 and Excel 2024, but not in every older perpetual version. In that case, Power Query or the split-then-transpose approach can work without that function. If a formula returns one unsplit value, verify that the delimiter in the formula exactly matches the characters in the source, including spaces or line breaks.

If Text to Columns appears to overwrite nearby content, undo immediately with Ctrl+Z on Windows and make sure the cells to the right of the source are empty before running the wizard again. The command writes results into adjacent cells. Likewise, when transposing, choose a blank destination with sufficient room so the pasted range does not replace existing data.

Frequently Asked Questions

Can Text to Columns split text into rows by itself?
No. Text to Columns creates separate fields across columns. Use TEXTSPLIT with a row delimiter, Power Query’s Split into Rows option, or transpose a horizontal result.

What formula splits a comma-separated cell vertically?
With the text in A2 and the formula entered in B2, use =TEXTSPLIT(A2,,","). The items spill down the rows beneath B2, provided the output cells are clear.

Can I split line breaks into separate rows?
Yes. With multi-line text in A2, enter =TEXTSPLIT(A2,,CHAR(10)) in B2. Each line becomes a separate row in the spill range.

Will the vertical results update when the source changes?
A TEXTSPLIT formula updates when its referenced text changes. A transpose paste does not update automatically. Power Query results can be refreshed after the source data or query changes.

Can I do this in older Excel versions?
Yes, but the available method depends on the version. Use Text to Columns followed by Paste > Transpose for a one-time result, or use Power Query if it is available in your Excel edition.

Bottom Line

You cannot make Excel’s Text to Columns wizard split directly downward. For a live vertical split in Microsoft 365, use TEXTSPLIT with the row delimiter; for repeatable table transformations, use Power Query; and for a quick static result, split across columns and paste with Transpose.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.