Text split to array

To split text with a delimiter into an array of values, you can use the TEXTSPLIT function. In the example shown, we are working with comma-separated values, so a comma (",") is the delimiter. The formula in cell D5, copied down, is: =TEXTSPLIT(B5,",") TEXTSPLIT returns an array of separate values to cell D5, and the five values spill into the range D5:H5. Note: in older versions of Excel without TEXTSPLIT, you can use a more complicated formula based on the FILTERXML function with help from the SUBSTITUTE and TRANSPOSE functions. Both approaches are explained below. If you don't want to use a formula, you can use Excel's Text-to-Columns feature.

April 14, 2025 · 1 min · 141 words · Mrs. Nicole Smith

Table of Contents

Explanation

In this example, the goal is to split thetext stringsin column B into anarrayof values.

TEXTSPLIT is a great way to solve this problem, because it is so simple to use.

In Excel 365, the array will spill into the range D5:H5 automatically.

Splitting comma-separated text with FILTERXML function

I learned the FILTERXML trick from Bill Jelen in aMrExcel video.

FILTERXML is not available in Excel on the Mac, or in Excel Online.

To do that, you’ve got the option to use the INDEX function.

Splitting comma-separated text with FILTERXML function

This approach requires a bit more setup.

However, if you only need a one-off manual process, you’re free to also use Excel’sText-to-Columnsfeature.

TEXTSPLIT can split text into rows or columns.

Splitting comma-separated text with FILTERXML function

FILTERXML Function

The Excel FILTERXML function returns specific data from XML text using the specified XPath expression.

SUBSTITUTE Function

The Excel SUBSTITUTE function replaces text in a given string by matching.

Splitting comma-separated text with FILTERXML function

Splitting comma-separated text with FILTERXML function

Excel formula: Split text with delimiter

Excel formula: Get first word

Excel formula: Extract nth word from text string

Excel formula: Extract word that begins with specific character

Excel formula: Extract multiple lines from a cell

Excel formula: Split text and numbers

Excel formula: Split text string at specific character

Excel TEXTSPLIT function

Excel FILTERXML function

Excel SUBSTITUTE function

Excel TRANSPOSE function

Splitting comma-separated text with FILTERXML function