How to make multiple phrases in my cell (seperated by commas) go into their own cell

[jeremypyle]

Hi,

I have the following in excel:

a1=red apple
a2=green grapes, yellow bananas, orange oranges
a3=black grape

I want a formula that I can put into b1 and then simply drag the formula down. I want the results to be as follows:

b1=red apple
b2=green grapes
b3=yellow bananas
b4=orange oranges
b5=black grape

I'm not sure if this is a simple formula or not. I can't figure it out. I have thousands of items in column A.

Anyone able to help with this?

 

[Damon Ostrander]

Hi Jeremy,

What you describe is exceedingly difficult to do with a worksheet formula (perhaps even a MrExcel Challenge of the Month candidate). However, it is not difficult at all to do with a VBA macro. Are you interested in entertaining a macro solution?

Damon

 

[s hal]

Do you only want the UNIQUE results? Or you want a long list of all the fruits? Is there a reason for wanting this? Sometimes the best solution is the one you aren't on the path for...