Split One-Cell String of Data by Comma, into Multiple Cells

[kintho]

Good day,

Say I am importing a list of the number of doughnuts sold to my customers from an external database, and this list comes in the form of a string. I import it to one cell in Excel, let's say cell B1, and the list is "1,6,2,8,3,10,1004,12". Let's say this were today, then today I have a data string of 8 customers.

Each purchase is separated by a comma, representing the next customer number for that day. The value stored between each comma would be the number of doughnuts that customer purchased.

I want to populate individual cells according to the number of doughnuts sold to that customer number, given that this list will be variable in length AND variable in doughnuts sold to each customer, day by day. In other words, there is not going to necessarily be 8 customers tomorrow, I may have less and I may have more. Furthermore, I may have any whole-number of doughnuts sold to every customer tomorrow, but I want to use the same formula to populate my data cells.

I've attached my hypothetical problem. How on earth could I go about doing this? I can't get the FIND, SEARCH, and MAX functions to help me. Any suggestions?

[mikeava]

So you want to break each item up?

Click on B1
Go to Data tab
Click on Text to Columns
Check Delimited
Click Next
Make sure only Comma is checked
Then Finish

[mikeava]

Attached what it looks like. Then you can just copy the cells down to row 5

[kintho]

Is there an automated method to do this using cell formulas? Once again, I may sell 4000000 customer's worth of doughnuts, say. Therefore, I would not want to populate cells by hand every time I dump data to the worksheet. Thank you for the reply.

[mikeava]

I could be wrong, but I would think formulas might be tricky depending on the number of entries you have.

I would think you would need a power query for this.

Can anybody else help out?

[AliGW]

In B5 copied across:

=TRIM(MID(SUBSTITUTE($B1,",",REPT(" ",100)),COLUMN(B1)*100-99,100))

[kintho]

Thanks! Works a charm; solved!