Excel Formulas to Split Text Strings into Rows

Manually transposing comma-separated strings into vertical rows remains a tedious, error-prone struggle for data analysts. While standard features like the "Text to Columns" wizard offer horizontal separation, they fail to align data vertically. Modern Excel formulas grant immediate, dynamic restructuring, eliminating repetitive manual work. Note the stipulation: this streamlined approach requires Excel 365 to support advanced array functions. By leveraging TEXTSPLIT nested within TOCOL-or utilizing row delimiters directly-you can transform your data layouts instantly. Below, we examine the precise formula construction to seamlessly convert your delimited strings into clean, organized rows.

When working with data exports from CRMs, ERPs, or external databases, you will frequently encounter the challenge of multi-value strings packed into a single cell. Often separated by commas, semicolons, or line breaks, these strings are notoriously difficult to analyze. While Microsoft Excel's traditional Text to Columns feature allows you to split these strings horizontally across columns, doing so can make your spreadsheet wide, unmanageable, and virtually impossible to query using functions like VLOOKUP, XLOOKUP, or FILTER.

For modern data analysis, splitting a delimited string vertically into rows instead of columns is the preferred approach. It organizes your data into a clean, normalized list ready for analysis, charting, or pivot tables. In this comprehensive guide, we will explore the best Excel formulas and techniques to split strings into rows, ranging from the latest dynamic array functions to legacy workarounds and Power Query.

Method 1: The Modern Excel Way – Using TEXTSPLIT (Microsoft 365 & Excel 2024)

If you are using Microsoft 365 or Excel 2024, the absolute easiest and most efficient way to split strings into rows is by using the TEXTSPLIT function. While most users think of TEXTSPLIT as a tool to split text across columns, its syntax is natively designed to handle vertical splitting as well.

Understanding the TEXTSPLIT Syntax

To understand how this works, let's look at the syntax of the function:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• text: The text or cell reference containing the string you want to split.
• col_delimiter: The character that marks where to split the text into columns (horizontal).
• row_delimiter: The character that marks where to split the text into rows (vertical).

By simply leaving the second argument (col_delimiter) blank and passing your delimiter to the third argument (row_delimiter), Excel will automatically spill the split text downwards into rows.

Step-by-Step Example: Splitting Comma-Separated Values

Imagine you have a cell (A2) containing the following text string: "Apple, Banana, Cherry, Date".

To split this string into vertical rows, enter the following formula in cell B2:

=TEXTSPLIT(A2, , ", ")

Note the double comma in the formula. By skipping the first delimiter argument, we instruct Excel to bypass splitting across columns and instead split the string at every comma followed by a space (", ") down into separate rows.

Alternative: TEXTSPLIT + TRANSPOSE

If you find skipping arguments confusing, you can achieve the exact same result by splitting the string horizontally first and then wrapping the entire formula inside the TRANSPOSE function:

=TRANSPOSE(TEXTSPLIT(A2, ", "))

Both methods yield the same output, though using the native third argument of TEXTSPLIT is slightly more efficient as it uses a single function call.

Method 2: Splitting Strings Separated by Line Breaks (Alt+Enter)

Another common scenario is dealing with cells where list items are separated by manual line breaks (created by pressing Alt + Enter in Excel). Visually, these look like lists within a single cell.

To split strings separated by line breaks, you must reference the line break character using the CHAR(10) function in Windows (or CHAR(13) on Mac OS). Wrap this inside your TEXTSPLIT formula as the row delimiter:

=TEXTSPLIT(A2, , CHAR(10))

This formula immediately transforms a single multi-line cell into a clean, vertical, dynamic array of individual rows.

Handling Spacing Issues: Adding the TRIM Function

Often, delimited data is inconsistent. Some entries might have spaces after commas, while others do not (e.g., "Apple,Banana, Cherry ,Date"). If you split this directly, you will end up with messy leading or trailing spaces in your rows, which can break future XLOOKUP formulas.

To prevent this, always wrap your split formula inside the TRIM function. TRIM removes all leading, trailing, and extra spaces from the split text:

=TRIM(TEXTSPLIT(A2, , ","))

Method 3: The Legacy Excel Workaround – FILTERXML (Excel 2013 to 2021)

If you are using an older version of Excel that does not support TEXTSPLIT, you can use a clever XML-based workaround. This method utilizes the FILTERXML and SUBSTITUTE functions. Note that this method is supported only on Excel for Windows.

The Logic Behind FILTERXML

The goal of this method is to convert your delimited string into a basic XML structure and then extract the nodes vertically. We will transform a string like "Apple,Banana,Cherry" into "<t><s>Apple</s><s>Banana</s><s>Cherry</s></t>".

The Formula

Assuming your text is in cell A2, use the following formula to split it vertically:

=FILTERXML("<t><s>" & SUBSTITUTE(A2, ",", "</s><s>") & "</s></t>", "//s")

How It Works:

1. SUBSTITUTE(A2, ",", "</s><s>"): Replaces every comma with an XML closing element tag and a new opening tag.
2. "<t><s>" & ... & "</s></t>": Wraps the beginning and end of the converted string with the root tag (<t>) and cell tags (<s>) to make it a valid XML document.
3. FILTERXML(..., "//s"): Queries the XML document and extracts all data inside the <s> elements. Because XML query results natively spill vertically in Excel, this automatically outputs your items into rows.

Method 4: The Scale Solution – Power Query

If you have a table containing hundreds of rows, where each row has a comma-separated list of items, using formula-based splitting can become slow or create messy overlapping arrays. For large datasets, Power Query is the ultimate solution.

Consider the following raw table:

To split the products into rows while retaining their corresponding Order IDs, follow these steps:

1. Select your table, go to the Data tab on the Ribbon, and click From Sheet (or From Table/Range). This opens the Power Query Editor.
2. Right-click the header of the column you want to split (e.g., "Products purchased").
3. Select Split Column > By Delimiter.
4. In the pop-up window, select your delimiter (e.g., Comma).
5. Expand the Advanced options section. This is the crucial step.
6. Under "Split into", choose Rows instead of Columns.
7. Click OK. Power Query will automatically duplicate the rows for other columns (like Order ID) to match each split item.
8. Go to the Home tab and click Close & Load to return the cleaned, normalized data back into a new Excel worksheet.

Comparison: Which Method Should You Use?

To help you decide which approach is best suited for your specific workbook, review the comparison below:

Troubleshooting Common Errors

• #SPILL! Error: If your TEXTSPLIT or FILTERXML formula returns a #SPILL! error, it means there are already values or formulas in the cells directly below your formula. Clear those cells to allow the formula to expand downward.
• #VALUE! with FILTERXML: This usually means your text contains characters that violate XML syntax rules (like ampersands & or angle brackets < or >). You must clean or substitute those characters before applying the formula.

Conclusion

Formatting multi-value strings vertically is essential for accurate Excel reporting. For users on Microsoft 365, utilizing TEXTSPLIT with skipped column arguments is the fastest, cleanest, and most modern formula path. When working with larger, structured tables, turning to Power Query ensures your workflows remain scalable and highly performant.