Today we are going to discuss about different methods in excel to split Comma separated texts into multiple cells. Please see below picture to get clarity in requirement.

Lot to learn, let's dive in.

With LAMBDA - Office 365:

Formula resides in D4 cell and spills the results across rows and columns.

Formula in D4 cell,

=DROP(REDUCE("",B4:B6,LAMBDA(State,Current,VSTACK(State,TEXTSPLIT(Current,", ")))),1)

Let me start this from scratch,

Syntax of REDUCE function,

=REDUCE(initial_value,array,function)

This is one of the LAMBDA function, which will do the given custom function on each iteration of array and will deliver only final result.

TEXTSPLIT function can deliver arrays as output, but it can't take array texts as input.

Syntax:

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

Let me show you an example,

Here, we fed single cell in text argument of TEXTSPLIT function and output meets our requirement. But,

When we fed entire range in text argument, this function only delivers the first item in each cell. Excel experts found a way to overcome this, by using VSTACK in LAMBDA fx argument in REDUCE function.

In our case, we used 2 variables named 'State' and 'Current' in LAMBDA function,

You can use any names based on your requirement but basic naming conventions need to be followed.

'State' is the output of previous iteration and can act as the input for current iteration.

'Current' is the current value in array for each iteration.

If we take iteration of B4 cell,

We pick B4 cell and split text by Comma as column delimiter. Then, we append or do vertical stacking with previous output or State using VSTACK function.

Same process will be carried out in each cell in the range B4:B6.

Since, we started with zero length text string, first row in our final output will have blanks and errors based on resulting columns as shown below.

To drop the first row, we wrap entire construction inside DROP function and fed 1 in rows argument.

Without LAMBDA - Office 365:

Simplest of all versions, but we should drag the formula down based on number of rows in our range.

Formula in I4 cell,

=TEXTSPLIT(B4,", ")

Classic version:

Formula in D11 cell,

=TRIM(MID(SUBSTITUTE($B4,", ",REPT(" ",LEN($B4))),((COLUMNS($D$11:D11)-1)*LEN($B4))+1,LEN($B4)))

We already discussed about this in our below post,

https://www.excel-end.com/post/method-to-split-words-from-one-cell-to-multiple-cells-using-text-functions

2013 or above version:

Formula in I11 cell,

=INDEX(IFERROR(FILTERXML("<A><B>"&SUBSTITUTE($B4,",","</B><B>")&"</B></A>","//B"),""),COLUMNS($I$11:I11))

We already discussed about this in our below post,

https://www.excel-end.com/post/method-to-split-words-from-one-cell-to-multiple-cells-using-filterxml-function