Comma separate values into rows

H

Hiten_pan

New Member
Joined
Mar 13, 2018
Messages
24
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.
 
Sort by date Sort by votes

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hiten_pan said:
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
Click to expand...

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


Book1
DE
19883;3883;0;405;0;5670;0;0;6160;1718;3191;3203;0;1
298836160
338831718
44053191
55673203
61
7
8
Sheet33
Cell Formulas
RangeFormula
D2=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&D$1,";0",""),";",REPT(" ",100)),ROW(D1)*100,100))+0,"")


D2 formula copied down and across.
 
Upvote 0
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.
[TABLE="width: 64"]


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

[TD="class: xl66, width: 64, bgcolor: transparent"]6473[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]6379[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]768[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]6704[/TD]

[/TR]

[TR]

[TD="class: xl67, bgcolor: transparent"]6240[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]36[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]9079[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]9079[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]2310[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]4809[/TD]

[/TR]

[TR]

[TD="class: xl66, bgcolor: transparent"]4280[/TD]

[/TR]


</tbody>[/TABLE]
 
Upvote 0
Did you Not try my formula in Post #12 ??
Works for me...


Book1
EF
10;0;0;6160;1718;3191;3203;0;16473;6473;6379;768;0;0;6704;6240;36;9079;9079;2310;4809;4280
261606473
317186473
431916379
53203768
616704
76240
836
99079
109079
112310
124809
134280
Sheet33
Cell Formulas
RangeFormula
E2=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&E$1,";0",""),";",REPT(" ",100)),ROW(E1)*100,100))+0,"")


E2 formula copied down and across.
 
Upvote 0
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?
[TABLE="width: 113"]
<tbody>[TR]
[TD]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[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 
Upvote 0
Hiten_pan said:
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?
[TABLE="width: 113"]
<tbody>[TR]
[TD]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
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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


Excel 2010
CE
10;0;0;6160;1718;3191;3203;0;10;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
26160680
317181734
4319114603
5320314603
6114603
714603
814603
914603
1014603
1114603
1214603
1314603
1414603
1514603
1614603
1714603
184719
196121
206160
216379
229610
237
24298
251756
2617682
274975
28795
Sheet13
Cell Formulas
RangeFormula
C2=IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(";"&C$1,";0",""),";",REPT(" ",100)),ROW(C1)*100,100))+0,"")
 
Last edited:
Upvote 0
If you are willing to try a VBA macro, the following one should do what you are asking for...
Code: 
[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.
 
Upvote 0
Hiten_pan said:
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.
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: transparent"]6473
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]6379
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]768
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]6704
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]6240
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]36
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]9079
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]9079
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]4809
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]4280
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Last edited:
Upvote 0
Hiten_pan said:
if you see 5th last row, it shows "7" instead of "7298. I was getting such incorrect data values.
Click to expand...

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
10;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
2680
31734
414603
514603
614603
714603
814603
914603
1014603
1114603
1214603
1314603
1414603
1514603
1614603
1714603
184719
196121
206160
216379
229610
237298
241756
2517682
264975
27795
2812345
2945678
Sheet13
Cell Formulas
RangeFormula
E2=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&E$1,";0",""),";",REPT(" ",100)),ROW(E1)*101,100)+0,"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Extra Commas in Exported CSV
Replies
8
Views
185
tomgrandy
T
Rob_010101
  • Solved
VBA to turn a row into values only (based on list item)
Replies
3
Views
59
Peter_SSs
Peter_SSs
R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
100
Rokcmd
R
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
59
RoryA
RoryA
S
  • Solved
Return Boolean (TRUE / FALSE)
Replies
3
Views
257
sharshra
S

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top