Remove Comma's from Cell

[Jyotirmaya]

I have values like this in the cell E6

1177,1178,,,,,1185,,,,,,1344,,,,,,1346,,,,,1251,,,1487,,,,,,1488,,,,,1489,,,,,,1348,,,,,1481,,,,,1485,,,,,,1486

I want these values in separate rows on F column, what should be the formula ?

 

[Nishant94]

You can use this:

=MID(TRIM(SUBSTITUTE($E$6,","," ")),
       5*(ROWS(F$6:F6)-1)+1,4)+0

 

[Jyotirmaya]

You can use this:

=MID(TRIM(SUBSTITUTE($E$6,","," ")),
       5*(ROWS(F$6:F6)-1)+1,4)+0

Yes this works, but I have similar values in E7 E8 upto E1000, so I want a formula where if I will drag the code in F column then automatically without comma it should show, now I need to do it separately for every cell, any macro will be better.

 

[Nishant94]

Do you want something like this:

Unknown
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
6	1177,1178,,,,,1185,,,,,,1344,,,,,,1346,,,,,1251,,,1487,,,,,,1488,,,,,1489,,,,,,1348,,,,,1481,,,,,1485,,,,,,1486	1177	1178	1185	1344	1346	1251	1487	1488	1489	1348	1481	1485	1486
7	5013,3617,4437,4266,,,,,3155,4198,7963,,,,,1929,,,,7870,,,,,1034,,,9145,,4226,3240	5013	3617	4437	4266	3155	4198	7963	1929	7870	1034	9145	4226	3240
8	1657,,8318,,,,,3049,,,,,1008,3110,,,8202,,,,,3385,1337,1420,,,8937,,,,4437,1202,,8515	1657	8318	3049	1008	3110	8202	3385	1337	1420	8937	4437	1202	8515
9	2749,,,,5626,,,7632,,,,,2758,,,,,7448,,,,8046,8420,,,8472,,,,,2503,,,,,8904,,,1242,6302,,4410	2749	5626	7632	2758	7448	8046	8420	8472	2503	8904	1242	6302	4410
10	1896,,,,8022,,,5550,,,,1473,,6385,,,4014,,5850,,1739,,,5908,9028,,2375,,8248,2416	1896	8022	5550	1473	6385	4014	5850	1739	5908	9028	2375	8248	2416
Sheet4

Cell Formulas
Range	Formula
F6	=MID(TRIM(SUBSTITUTE($E6,","," ")),5*(COLUMNS($F6:F6)-1)+1,4)+0

 

[Rick Rothstein]

Yes this works, but I have similar values in E7 E8 upto E1000, so I want a formula where if I will drag the code in F column then automatically without comma it should show, now I need to do it separately for every cell, any macro will be better.

In your first message, you said the value in cell E6 should be down Column F... where should the value in E7 go, down Column G or down Column F underneath the values from cell E6?