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.

 

[kvsrinivasamurthy]

Second formula above works exactly same way.

 

[jtakw]

I tried this formulae in excel 2007 but throws me error [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Value]#Value [/URL] . Can u plz check again

Don't know why you're getting #Value error, unless you're talking about further down the column, try this:

Book1
	D	E
1	9883;3883;0;405;0;567	0;0;0;6160;1718;3191;3203;0;1
2	9883	6160
3	3883	1718
4	405	3191
5	567	3203
6		1
7
8
Sheet33

Cell Formulas
Range	Formula
D2	=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&D$1,";0",""),";",REPT(" ",100)),ROW(D1)*100,100))+0,"")

D2 formula copied down and across.

 

[Hiten_pan]

6473;6473;6379;768;0;0;6704;6240;36;9079;9079;2310;4809;4280

when I use this formulae it excludes zero but it exclude the first number and returns as shown below
=TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(A$2,";0",""),";",REPT(" ",100)),ROW(A2)*100,100))

if you see below, it skips first item of the string. Plz advise.

6473
6379
768
6704
6240
36
9079
9079
2310
4809
4280

<colgroup><col width="64" style="width: 48pt;">
<tbody>

































</tbody>

 

[jtakw]

Did you Not try my formula in Post #12 ??
Works for me...

Book1
	E	F
1	0;0;0;6160;1718;3191;3203;0;1	6473;6473;6379;768;0;0;6704;6240;36;9079;9079;2310;4809;4280
2	6160	6473
3	1718	6473
4	3191	6379
5	3203	768
6	1	6704
7		6240
8		36
9		9079
10		9079
11		2310
12		4809
13		4280
Sheet33

Cell Formulas
Range	Formula
E2	=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&E$1,";0",""),";",REPT(" ",100)),ROW(E1)*100,100))+0,"")

E2 formula copied down and across.

 

[Hiten_pan]

ok, I know what's the problem. This string can vary may be upto 3000 characters. this formula doesn't work when the string increases by length. Can you please help?

0;680;1734;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;4719;6121;6160;6379;9610;7298;1756;17682;4975;795

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

 

[jtakw]

ok, I know what's the problem. This string can vary may be upto 3000 characters. this formula doesn't work when the string increases by length. Can you please help?

0;680;1734;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;4719;6121;6160;6379;9610;7298;1756;17682;4975;795

<tbody>
</tbody>

I still don't see your problem, my formula is working for Every sample you've given:

Excel 2010
	C	E
1	0;0;0;6160;1718;3191;3203;0;1	0;680;1734;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;4719;6121;6160;6379;9610; 7298;1756;17682;4975;795
2	6160	680
3	1718	1734
4	3191	14603
5	3203	14603
6	1	14603
7		14603
8		14603
9		14603
10		14603
11		14603
12		14603
13		14603
14		14603
15		14603
16		14603
17		14603
18		4719
19		6121
20		6160
21		6379
22		9610
23		7
24		298
25		1756
26		17682
27		4975
28		795
Sheet13

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

 

[Rick Rothstein]

If you are willing to try a VBA macro, the following one should do what you are asking for...

[table="width: 500"]
[tr]
	[td]Sub SplitDown()
  Dim Cell As Range, Arr() As String
  Application.ScreenUpdating = False
  For Each Cell In Range("A1", Cells(1, Columns.Count).End(xlToLeft))
    Arr = Split(Application.Trim(Replace(" " & Replace(Cell.Value, ";", " ") & " ", " 0", " ")))
    Cell.Offset(1).Resize(UBound(Arr) + 1) = Application.Transpose(Arr)
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (SplitDown) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jtakw]

6473;6473;6379;768;0;0;6704;6240;36;9079;9079;2310;4809;4280

when I use this formulae it excludes zero but it exclude the first number and returns as shown below
=TRIM(MID(SUBSTITUTE(";"&SUBSTITUTE(A$2,";0",""),";",REPT(" ",100)),ROW(A2)*100,100))

if you see below, it skips first item of the string. Plz advise.

6473
6379
768
6704
6240
36
9079
9079
2310
4809
4280

<tbody>
</tbody>

I think I know why you're having problems using my formula, looks like you changed the Cell reference in the formula according to your data, which is perfectly fine, However, do NOT change the Cell reference Bolded in Red above, that Has to Be A1, B1, or C1, etc.

This should solve your problem.

Remember to use my formula in Post #12 , #14 , #16 (same formula), to avoid the #VALUE error.

 

[Hiten_pan]

if you see 5th last row, it shows "7" instead of "7298. I was getting such incorrect data values.

 

[jtakw]

if you see 5th last row, it shows "7" instead of "7298. I was getting such incorrect data values.

OK, I see it, there's a space between ; and 7298, is this a typo, or your actual data might contain spaces randomly?
I removed the space and tweaked my formula a little, seems to work.
Let me know about the random space question.

Excel 2010
	E
1	0;680;1734;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;14603;4719;6121;6160;6379;9610;7298;1756;17682;4975;795;12345;45678
2	680
3	1734
4	14603
5	14603
6	14603
7	14603
8	14603
9	14603
10	14603
11	14603
12	14603
13	14603
14	14603
15	14603
16	14603
17	14603
18	4719
19	6121
20	6160
21	6379
22	9610
23	7298
24	1756
25	17682
26	4975
27	795
28	12345
29	45678
Sheet13

Cell Formulas
Range	Formula
E2	=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&E$1,";0",""),";",REPT(" ",100)),ROW(E1)*101,100)+0,"")