Comma separate values into rows

[Hiten_pan]

I have data values which has values for eg: 9883;3883;45;567 which i want to convert into rows with use of formula. Plz advise.

 

[mikenelena]

What format is the data in now? Columns? A single cell?

 

[Hiten_pan]

It is in single cell, this data also has zeros in between the string which i want to ignore. The data with zero will look like 9883;3883;0;405;0;567

 

[jtakw]

Hi,

Here's a formula solution for what you described:

Book1
	A	B	C	D
1	9883;3883;45;567	9883;3883;0;405;0;567	9883;3883;45;567	9883;3883;0;405;0;567
2	9883	9883	9883	9883
3	3883	3883	3883	3883
4	45	405	45	405
5	567	567	567	567
Sheet33

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(A$1,";0",""),";",REPT(" ",100)),ROW(A1)*100,100))
C2	=TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(C$1,";0",""),";",REPT(" ",100)),ROW(C1)*100,100))+0

Use A2 formula if you don't need the result converted to a Real Number, use C2 formula if you do.

 

[Hiten_pan]

Looks wonderful, i will try this. Thanks a ton

 

[Hiten_pan]

I tried this formulae in excel 2007 but throws me error #Value . Can u plz check again

 

[kvsrinivasamurthy]

In B2
9883;3883;45;567

In B3 then drag down

=IFERROR(0+TRIM(MID(SUBSTITUTE($B$2,";",REPT(" ",200)),1+200*(ROWS($B$2:$B2)-1),200)),"")

 

[Hiten_pan]

This works but only problem is it doesn't skip zeros. For this string 9883;3883;0;405;0;567

 

[kvsrinivasamurthy]

Try this. This avoids 0 but it leaves cell BLANK.

=IFERROR(1/(1/(0+TRIM(MID(SUBSTITUTE($B$2,";",REPT(" ",200)),1+200*(ROWS($B$2:$B2)-1),200)))),"")

This formulas avoids 0 completely and other values are listed.

=IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE($B$2,"0;",""),";",REPT(" ",200)),1+200*(ROWS($B$2:$B2)-1),200)),"")

 

[Hiten_pan]

to be more precise I have the following string

0;0;0;6160;1718;3191;3203;0;1

<tbody>
</tbody>

this should look like below:

6160

1718

3191

3203

1

<tbody>
</tbody><colgroup><col></colgroup>