Split data into columns using FILTERXML

May 22, 2021

We use Text to Columns to split data from a cell into multiple columns.
But it is not dynamic.

The data looks like this:

This is the result:

Formula in C3:

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

You can also use LAMBDA:

=LAMBDA(input,delimiter,TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(input,delimiter,"</y><y>")&"</y></x>","//y")))

Formula in C4:

=Split_Text(B4,",")

Let us see some more examples:

To access a single value, we can use Index.
Let us say we only want the first two values.

Formula in C3:

=INDEX(TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(B3,",","</y><y>")&"</y></x>","//y")),{1,2})

Another use case will be when you want to add numbers that are split by a delimiter.

Formula in C3:

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

Written by HAVISH MADHVAPATY