Extract list - Comma separated

Morning All

I'm looking for a formula option only for this query.

Excel 2010/2013

I need to extract a list of single items from a comma delimited string in Column A, each item is always separted by a comma except when there's only 1 item in that string and in that case it will just be the item name with no delimter.

I don't want to use text to columns as there's loads of manually faffing about

The attached file will explains what I need

Extract list comma delimited.xlsx

Thanks

Colin

see the attached file data was extracted with 2 supporting columns

Or, if you want a solution without the use of helper columns, go to Name Manager and create the following:

Name: Arry1
Refers to: =1+LEN($A$2:$A$8)-LEN(SUBSTITUTE($A$2:$A$8,",",""))

Name: Arry2
Refers to: =MMULT(0+(ROW($A$2:$A$8)>=TRANSPOSE(ROW($A$2:$A$8))),Arry1)

Exit Name Manager.

The array formula** in your first cell of choice is then:

=IF(ROWS($1:1)>SUM(Arry1),"",INDEX(TRIM(MID(SUBSTITUTE(INDEX($A$2:$A$8,MATCH(TRUE,Arry2>=ROWS($1:1),0))&",",",",REPT(" ",999)),999*(ROW(INDIRECT("1:999"))-1)+1,999)),IFERROR(1+ROWS($1:1)-LOOKUP(ROWS($1:1),1+Arry2),ROWS($1:1))))

Copy down until you start to get blanks for the results.

Regards

Edit: note that this set-up requires that there are no blank cells within the range, which I took to be a fair assumption since you gave no indication otherwise and that indeed was the case with the example you posted.


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually inserat these yourself).

Another option to achieve this is to take help of a VBA code. If you are open to a VBA solution to this, please find the attached sheet and click on the Green Button to generate the desired list.

Thanks guys, all solutions work perfectly

Thanks for your feedback and adding the Reputation Mr/s. TheCman81

You're welcome. Thanks for the feedback.