Exceljet

Quick, clean, and to the point

Text split to array

Excel formula: Text split to array
Generic formula 
=FILTERXML("<x><y>"&SUBSTITUTE(A1,",","</y><y>")&"</y></x>","//y")
Summary 

To split text with a delimiter and transform the result into an array, you can use the FILTERXML function with help from the SUBSTITUTE and TRANSPOSE functions. In the example shown, the formula in D5 is:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y"))

See below for a full explanation.

I learned this trick from Bill Jelen in a MrExcel video. FILTERXML is not available in Excel on the Mac, or in Excel Online. This is a nerdy workaround for difficult problem in older versions of Excel.The new TEXTSPLIT function in Excel 365 is a much easier way to solve this problem. 

New: TEXTSPLIT function in Excel 365
Explanation 

Older versions of Excel do not have a function dedicated to splitting text to an array, similar to the PHP explode function, or Python split method. As a workaround, you can use the FILTERXML function, after first adding XML markup to the text.

In the example shown, we have several comma-delimited text strings like this:

"Jim,Brown,33,Seattle,WA"

The goal is to split the information into separate columns using the comma as a delimiter.

The first task is to add XML markup to this text, so that it can be parsed as XML with the FILTERXML function. We are going to arbitrarily make each field in the text a element, enclosed with a parent element. We start with the SUBSTITUTE function here:

SUBSTITUTE(B5,",","</y><y>")

The result from SUBSTITUTE is a text string like this:

"Jim</y><y>Brown</y><y>33</y><y>Seattle</y><y>WA"

To ensure well-formed XML tags and to wrap all elements in a parent element, we prepend and append more XML tags like this:

"<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>"

This yields a text string like this (line breaks added for readability)

"<x>
  <y>Jim</y>
  <y>Brown</y>
  <y>33</y>
  <y>Seattle</y>
  <y>WA</y>
</x>"

This text is delivered directly to the FILTERXML function as the xml argument, with an Xpath expression of "//y":

FILTERXML("<x><y>Jim</y><y>Brown</y><y>33</y><y>Seattle</y><y>WA</y></x>","//y")

Xpath is a parsing language and "//y" selects all elements. The result from FILTERXML is a vertical array like this:

{"Jim";"Brown";33;"Seattle";"WA"}

Because we want a horizontal array in this instance, we wrap the TRANSPOSE function around FILTERXML:

=TRANSPOSE({"Jim";"Brown";33;"Seattle";"WA"})

The result is a horizontal array like this:

{"Jim","Brown",33,"Seattle","WA"}

which spills into the range D5:H5 in Excel 365.

Extract nth item

The formula can be extended with the INDEX function to extract the nth item. For example, to extract just the age, you can use INDEX with a row number of 1 and column number of 3:

=INDEX(D5#,1,3) // age only

Or, in an all-in-one formula:

=INDEX(TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y")),1,3)
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.