Hi - Dave here.
Happy Friday!
How do you split comma-separated values (CSV) with a formula?
This used to be a tricky problem in Excel, but these days you can simply use the TEXTSPLIT function:
=TEXTSPLIT(A1,",")
However, TEXTSPLIT will fail if you try to give it a range of values:
=TEXTSPLIT(A1:A10,",")
This is due to a limitation in Excel that will not allow a formula to return an "array of arrays". If you try to use TEXTSPLIT on a range, you'll get back just the first value.
It turns out that the REDUCE function is a great way to work around this limitation. You can see how this works below, where we use one formula in cell D5 to split all CSV text in B5:B15 in one step:
=DROP(REDUCE("",B5:B15,LAMBDA(a,v,VSTACK(a,TEXTSPLIT(v,",")))),1)
[
Download the workbook and read the full explanation]
Like the SCAN function, REDUCE can be difficult to understand, so good examples help a lot. I'll share more about REDUCE soon. In the meantime, you can find a full explanation and download the sample workbook at the link above. I've also included a simple way to make the formula dynamic, so that it will automatically expand to handle new data.
Note: REDUCE and TEXTSPLIT are only available in Excel 365 and Excel 2024. If you have an older version of Excel, the article does explain a workaround to split CSV values with the seldom seen FILTERXML function.
Excel formulas
We maintain a list of over 1000 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a detailed look at how to solve a specific problem in Excel. You can sign up on our home page.