How about an alternative:

With your data in A2:Axx, put this formula in B2:
=","&SUBSTITUTE(A2," ","")&","

Then B2 will look like:
,1,2,4,5,7,8,

Then put this in C2 and drag across as far as you need.
=IF(COUNTIF($B2,"*,"&COLUMN()-2&",*")0,COLUMN()-2,"")

Select B2 through L2 (as far right as you went).
Drag down.

If you want values, select that range
Edit|copy
edit|paste special|values

And delete columns A:B if you want.


jack wrote:

I have data in cells A2, A3 and A4 as below:
1, 2, 4, 5, 7, 8
1, 2, 3, 4
2, 3, 4

I want to split into columns (which I did using Data Text to Columns
option).

But I also want the data to "automatically" fall under appropriate heading
(1, 2, 3, 4, 5, 6, 7, 8) which are in respecive cells A1:H1.

Thank you in advance

--

Dave Peterson