formula for seperating text

[vidyavallaba]

Hello,

I am a new user.

I need a formula to separate text and place them into new cell.

example: a1:red,blue,green,yellow,.....

a2:red
a3:blue
a4:green
a5:yellow

i need excel formula to do this.Not text to column function or macros
can any one please help me.

 

[BrianMH]

This should help. You will need to make some modifications though.

Text to columns: Split words in a cell (excel array formula) | Get Digital Help - Microsoft Excel resource

 

[vidyavallaba]

This should help. You will need to make some modifications though.

Text to columns: Split words in a cell (excel array formula) | Get Digital Help - Microsoft Excel resource

Hi,
Thanks for the reply. But the formula i was directed to is too confusing and too complicated... I need something simple...

 

[Rick Rothstein]

Hi,
Thanks for the reply. But the formula i was directed to is too confusing and too complicated... I need something simple...

Then you did not read all the comments to see the rather simple formula I offered in one of my comments there. To save you the trouble of trying to find it, and figuring out how to modify it for copying down instead of across, put this formula in A2 and copy it down as far as you think you will ever have need for, either now or in the future...

=TRIM(MID(SUBSTITUTE(A$1,",",REPT(" ",99)),ROW(A1)*99-98,99))

 

[vidyavallaba]

Thanks a lot... It worked