Comma separate values into rows

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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Today I encountered with an issue where cell goes blank or displays single value. Would you mind checking this one?

16600;1426;3388;4773;9275;9610;20001;6162;20001;9610;1745;6121;6379;20001;20001;781;768;6908;9131;1426;1750;1782;1788;3253;3377;4773;9131;9431;20001;9094;3253;9610;6160;1750;1756;1766;1772;2310;6160;9882;9610;0;1322;1322;1422;2172;2462;17682;2310;9610;2310;9431;9610;9275;6177;6210;4809;1756;6121;1747;2310;1717;2287;1718;2493;6160;9610;1257;1766;2310;2329;9094;9114;9275;9610;10700

16600
1426
3388
4773
9275
9610
20001
6162
20001
9610
1745
6121
6379
20001
20001
781
768
6908
9131
1426
1750
1782
1788
3253
3377
4773
9131
9431
20001
9094
3253
961
160
1750
1756
1766
1772
2310
6160
9882
9610
1322
1322
1422
2172
2462
17682
2310
9610
2310
9431
9610
9275
6177
6210
4809
1756
6121
1747
2310
1717
2287
1718
2493
6160
9
10
1257
1766
2310
2329
9094
9114
9275
9610
10700


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







































































































































































































































</tbody>
 
Upvote 0
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.


Today I encountered with an issue where cell goes blank or displays single value. Would you mind checking this one?

16600;1426;3388;4773;9275;9610;20001;6162;20001;9610;1745;6121;6379;20001;20001;781;768;6908;9131;1426;1750;1782;1788;3253;3377;4773;9131;9431;20001;9094;3253;9610;6160;1750;1756;1766;1772;2310;6160;9882;9610;0;1322;1322;1422;2172;2462;17682;2310;9610;2310;9431;9610;9275;6177;6210;4809;1756;6121;1747;2310;1717;2287;1718;2493;6160;9610;1257;1766;2310;2329;9094;9114;9275;9610;10700

16600
1426
3388
4773
9275
9610
20001
6162
20001
9610
1745
6121
6379
20001
20001
781
768
6908
9131
1426
1750
1782
1788
3253
3377
4773
9131
9431
20001
9094
3253
961
160
1750
1756
1766
1772
2310
6160
9882
9610
1322
1322
1422
2172
2462
17682
2310
9610
2310
9431
9610
9275
6177
6210
4809
1756
6121
1747
2310
1717
2287
1718
2493
6160
9
10
1257
1766
2310
2329
9094
9114
9275
9610
10700

<tbody>
</tbody>

You never got back to me on my question, the problem here is that you have multiple random SPACES within the string, this updated formula should work:


Excel 2010
B
116600;1426;3388;4773;9275;9610;20001;6162;20001;9610;1745;6121;6379;20001;20001;781;768;6908;9131;1426;1750;1782;1788;32 53;3377;4773;9131;9431;20001;9094;3253;9610;6160;1750;1756;1766;1772;2310;6160;9882;9610;0;1322;1322;1422;2172;2462;1768 2;2310;9610;2310;9431;9610;9275;6177;6210;4809;1756;6121;1747;2310;1717;2287;1718;2493;6160;9610;1257;1766;2310;2329;909 4;9114;9275;9610;10700
216600
31426
43388
54773
69275
79610
820001
96162
1020001
119610
121745
136121
146379
1520001
1620001
17781
18768
196908
209131
211426
221750
231782
241788
253253
263377
274773
289131
299431
3020001
319094
323253
339610
346160
351750
361756
371766
381772
392310
406160
419882
429610
431322
441322
451422
462172
472462
4817682
492310
509610
512310
529431
539610
549275
556177
566210
574809
581756
596121
601747
612310
621717
632287
641718
652493
666160
679610
681257
691766
702310
712329
729094
739114
749275
759610
7610700
Sheet24
Cell Formulas
RangeFormula
B2=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(B$1," ",""),";0",""),";",REPT(" ",255)),ROW(B1)*256,255)+0,"")
 
Upvote 0
When i saw this issue, i first checked spaces but i couldn't find any, can u show me where did u see the space? Let me use this formulae in the meanwhile.
 
Upvote 0
just checked the new formulae, I am not sure what's the issue here. it goes off in line 58 and 59. I don't see any space or something new in the string.
It worked when I changed your formulae from 256 to 255. Can you check the logic if it's right?
=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(B$1," ",""),";0",""),";",REPT(" ",255)),ROW(B1)*256,255)+0,"")
to
=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(B$1," ",""),";0",""),";",REPT(" ",255)),ROW(B1)*255,255)+0,"")
99400;72818;432051;341140;156658;21349;90558;291542;90559;21351;323206;38476;69029;90499;90500;180465;2536143;152115;2101;72817;148564;46005;71131;76255;537828;341139;2102;48416;90557;117015;76256;21346;310013;148565;36700;72521;79088;269249;310014;258293;21350;0;290246;290247;569446;299638;464381;34900;269247;21347;269248;48417;21348;156659;269967;45128;176777;36701;38477;962050;268633;199591;199092;83285;207751;310012;21345;344018;72522;269250;160797;117016;31810;156660;21352;267850

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




</tbody>
99400
72818
432051
341140
156658
21349
90558
291542
90559
21351
323206
38476
69029
90499
90500
180465
2536143
152115
2101
72817
148564
46005
71131
76255
537828
341139
2102
48416
90557
117015
76256
21346
310013
148565
36700
72521
79088
269249
310014
258293
21350
290246
290247
569446
299638
464381
34900
269247
21347
269248
48417
21348
156659
269967
45128
176777
36701
3
477
962050
268633
199591
199092
83285
207751
310012
21345
344018
72522
269250
160797
117016
31810
156660
21352
267850

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




































































































































































































































</tbody>
 
Upvote 0
Hi,

I haven't forgotten about you, been thinking about this problem last few days...
Since your data is rather "Large", as the formula goes down, eventually, it gets thrown off, so I was thinking of a way to at least compensate for the "Long" dataset, here's what I came up with, think it should work nicely.

Also, there Are random spaces in this latest set of data (3, to be exact), see the results in G1 and H1, similarly with the data in your post #22 .


Excel 2010
EFGH
199400;72818;432051;341140;156658;21349;90558;291542;90559;21351;323206;38476;69029;90499;90500;180465;2536143;152115;210 1;72817;148564;46005;71131;76255;537828;341139;2102;48416;90557;117015;76256;21346;310013;148565;36700;72521;79088;26924 9;310014;258293;21350;0;290246;290247;569446;299638;464381;34900;269247;21347;269248;48417;21348;156659;269967;45128;176 777;36701;38477;962050;268633;199591;199092;83285;207751;310012;21345;344018;72522;269250;160797;117016;31810;156660;21352;267850492489
299400
372818
4432051
5341140
6156658
721349
890558
9291542
1090559
1121351
12323206
1338476
1469029
1590499
1690500
17180465
182536143
19152115
202101
2172817
22148564
2346005
2471131
2576255
26537828
27341139
282102
2948416
3090557
31117015
3276256
3321346
34310013
35148565
3636700
3772521
3879088
39269249
40310014
41258293
4221350
43290246
44290247
45569446
46299638
47464381
4834900
49269247
5021347
51269248
5248417
5321348
54156659
55269967
5645128
57176777
5836701
5938477
60962050
61268633
62199591
63199092
6483285
65207751
66310012
6721345
68344018
6972522
70269250
71160797
72117016
7331810
74156660
7521352
76267850
Sheet24
Cell Formulas
RangeFormula
G1=LEN(E1)
H1=LEN(SUBSTITUTE(E1," ",""))
E2=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(E$1," ",""),";0",""),";",REPT(" ",255)),ROW(E1)*256-4+4*ROWS(E$2:E2),255)+0,"")
 
Upvote 0
Too late to edit, use this instead, just a bit shorter:


Excel 2010
EFGH
199400;72818;432051;341140;156658;21349;90558;291542;90559;21351;323206;38476;69029;90499;90500;180465;2536143;152115;210 1;72817;148564;46005;71131;76255;537828;341139;2102;48416;90557;117015;76256;21346;310013;148565;36700;72521;79088;26924 9;310014;258293;21350;0;290246;290247;569446;299638;464381;34900;269247;21347;269248;48417;21348;156659;269967;45128;176 777;36701;38477;962050;268633;199591;199092;83285;207751;310012;21345;344018;72522;269250;160797;117016;31810;156660;21352;267850492489
299400
372818
4432051
5341140
6156658
721349
890558
9291542
1090559
1121351
12323206
1338476
1469029
1590499
1690500
17180465
182536143
19152115
202101
2172817
22148564
2346005
2471131
2576255
26537828
27341139
282102
2948416
3090557
31117015
3276256
3321346
34310013
35148565
3636700
3772521
3879088
39269249
40310014
41258293
4221350
43290246
44290247
45569446
46299638
47464381
4834900
49269247
5021347
51269248
5248417
5321348
54156659
55269967
5645128
57176777
5836701
5938477
60962050
61268633
62199591
63199092
6483285
65207751
66310012
6721345
68344018
6972522
70269250
71160797
72117016
7331810
74156660
7521352
76267850
Sheet24
Cell Formulas
RangeFormula
G1=LEN(E1)
H1=LEN(SUBSTITUTE(E1," ",""))
E2=IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(E$1," ",""),";0",""),";",REPT(" ",255)),ROW(E1)*256-4+4*ROW(E1),255)+0,"")
 
Upvote 0
0;9032;9781;16600;3108;4809;4773;9810;6160;2310;9431;9620;9810;1426;1426;3422;4773;16600;20001;4401;2306;6160;7667;9032;9781;2310;2148;520207;768;719;1426;3422;6598;9431;20001;2197;9862;526416;9862;20001;9620;16600;1747;2306;20001;7106;2310;2310;1747;2329;16600;6160;6160;1257;1747;23290;9655;17459;101469;78545;181359;342298;218175;311081;270822;48886;5709;218176;72946;72947;12144;342299;101470;91151;74113;128672;311084;211718;9656;17460;270550;218074;42849;2547896;988579;72945;12143;104796;48885;91150;393008;160562;49587;160563;91152;5710;101471;969357;128654;91153;39379;270823;270824;969358;161095;101472;311082;311083;344909;969359;161096
AcctTag#
90329655
7817459
6001469
878545
9181359
342298
6218175
23311081
943270822
962048886
98105709
1426218176
142672946
342272947
477312144
16600342299
20001101470
440191151
230674113
6160128672
7667311084
9032211718
97819656
231017460
2148270550
520207218074
76842849
7192547896
1426988579
342272945
659812143
9431104796
2000148885
219791150
9862393008
526416160562
986249587
20001160563
962091152
166005710
1747101471
2306969357
20001128654
710691153
231039379
2310270823
1747270824
2329969358
16600161095
6160101472
6160311082
1257311083
1747344909
2329969359
161096

<tbody>
</tbody><colgroup><col><col><col></colgroup>
 
Upvote 0
#28 Column A contains set of strings and B contains another set of strings in same manner


Formulae starts from cell B12/C12 respectively. can u plz check again? I see some discrepancies from line number 5. I am not seeing any spaces in the string. let me know if I can send u the file. Not sure how to attach here.
 
Upvote 0
#28 Column A contains set of strings and B contains another set of strings in same manner


Formulae starts from cell B12/C12 respectively. can u plz check again? I see some discrepancies from line number 5. I am not seeing any spaces in the string. let me know if I can send u the file. Not sure how to attach here.

Sorry didn't get back to you sooner.
I couldn't make heads or tails from your post above, it doesn't fit on screen...

You can upload your file to a free file sharing site like dropbox and post the link here.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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