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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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

[TABLE="width: 64"]


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

[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16600[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1426[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]3388[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]4773[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9275[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]20001[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6162[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]20001[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1745[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6121[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6379[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]20001[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]20001[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]781[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]768[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6908[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9131[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1426[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1750[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1782[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1788[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]3253[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]3377[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]4773[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9131[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9431[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]20001[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9094[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]3253[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]961[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent"] [/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]160[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1750[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1756[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1766[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1772[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2310[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6160[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9882[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1322[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1322[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1422[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2172[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2462[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]17682[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2310[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2310[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9431[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9275[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6177[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6210[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]4809[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1756[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6121[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1747[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2310[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1717[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2287[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1718[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2493[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]6160[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]10[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1257[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]1766[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2310[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]2329[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9094[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9114[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9275[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]9610[/TD]

[/TR]

[TR]

[TD="class: xl65, bgcolor: transparent, align: right"]10700[/TD]

[/TR]


</tbody>[/TABLE]
 
Upvote 0
jtakw said:
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.
Click to expand...


Hiten_pan said:
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

[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]16600
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1426
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3388
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4773
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9275
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6162
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1745
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6121
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6379
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]781
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]768
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6908
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9131
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1426
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1750
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1782
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1788
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3253
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3377
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4773
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9131
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9431
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]20001
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9094
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]3253
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]961
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]160
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1750
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1756
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1766
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1772
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6160
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9882
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1322
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1322
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1422
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2172
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2462
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]17682
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9431
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9275
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6177
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6210
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]4809
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1756
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6121
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1747
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1717
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2287
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1718
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2493
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]6160
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1257
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]1766
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2310
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2329
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9094
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9114
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9275
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]9610
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]10700
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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,"")
[TABLE="width: 64"]
[/TABLE]
[TABLE="width: 64"]
[/TABLE]
[TABLE="width: 64"]

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


[TD="width: 64, bgcolor: transparent"]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[/TD]


[/TR]

</tbody>[/TABLE]
[TABLE="width: 64"]


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

[TD="class: xl63, width: 64, bgcolor: transparent, align: right"]99400[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]72818[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]432051[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]341140[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]156658[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21349[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]90558[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]291542[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]90559[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21351[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]323206[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]38476[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]69029[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]90499[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]90500[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]180465[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]2536143[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]152115[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]2101[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]72817[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]148564[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]46005[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]71131[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]76255[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]537828[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]341139[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]2102[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]48416[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]90557[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]117015[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]76256[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21346[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]310013[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]148565[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]36700[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]72521[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]79088[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]269249[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]310014[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]258293[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21350[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]290246[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]290247[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]569446[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]299638[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]464381[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]34900[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]269247[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21347[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]269248[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]48417[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21348[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]156659[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]269967[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]45128[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]176777[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]36701[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]3[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]477[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]962050[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]268633[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]199591[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]199092[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]83285[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]207751[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]310012[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21345[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]344018[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]72522[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]269250[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]160797[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]117016[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]31810[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]156660[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]21352[/TD]

[/TR]

[TR]

[TD="class: xl63, bgcolor: transparent, align: right"]267850[/TD]

[/TR]


</tbody>[/TABLE]
 
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
[TABLE="width: 594"]
<tbody>[TR]
[TD]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;2329[/TD]
[TD]0;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[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Acct[/TD]
[TD]Tag#[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9032[/TD]
[TD="align: right"]9655[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]781[/TD]
[TD="align: right"]7459[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]1469[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]78545[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]181359[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]342298[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]218175[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]311081[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]943[/TD]
[TD="align: right"]270822[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9620[/TD]
[TD="align: right"]48886[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9810[/TD]
[TD="align: right"]5709[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1426[/TD]
[TD="align: right"]218176[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1426[/TD]
[TD="align: right"]72946[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3422[/TD]
[TD="align: right"]72947[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4773[/TD]
[TD="align: right"]12144[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16600[/TD]
[TD="align: right"]342299[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]101470[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4401[/TD]
[TD="align: right"]91151[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2306[/TD]
[TD="align: right"]74113[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6160[/TD]
[TD="align: right"]128672[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7667[/TD]
[TD="align: right"]311084[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9032[/TD]
[TD="align: right"]211718[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9781[/TD]
[TD="align: right"]9656[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2310[/TD]
[TD="align: right"]17460[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2148[/TD]
[TD="align: right"]270550[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]520207[/TD]
[TD="align: right"]218074[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]768[/TD]
[TD="align: right"]42849[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]719[/TD]
[TD="align: right"]2547896[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1426[/TD]
[TD="align: right"]988579[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]3422[/TD]
[TD="align: right"]72945[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6598[/TD]
[TD="align: right"]12143[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9431[/TD]
[TD="align: right"]104796[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]48885[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2197[/TD]
[TD="align: right"]91150[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9862[/TD]
[TD="align: right"]393008[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]526416[/TD]
[TD="align: right"]160562[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9862[/TD]
[TD="align: right"]49587[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]160563[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]9620[/TD]
[TD="align: right"]91152[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16600[/TD]
[TD="align: right"]5710[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1747[/TD]
[TD="align: right"]101471[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2306[/TD]
[TD="align: right"]969357[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20001[/TD]
[TD="align: right"]128654[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]7106[/TD]
[TD="align: right"]91153[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2310[/TD]
[TD="align: right"]39379[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2310[/TD]
[TD="align: right"]270823[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1747[/TD]
[TD="align: right"]270824[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2329[/TD]
[TD="align: right"]969358[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]16600[/TD]
[TD="align: right"]161095[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6160[/TD]
[TD="align: right"]101472[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]6160[/TD]
[TD="align: right"]311082[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1257[/TD]
[TD="align: right"]311083[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]1747[/TD]
[TD="align: right"]344909[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]2329[/TD]
[TD="align: right"]969359[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD="align: right"]161096[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]
 
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
Hiten_pan said:
#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.
Click to expand...

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
You must log in or register to reply here.

Similar threads

T
  • Solved
Extra Commas in Exported CSV
Replies
8
Views
208
tomgrandy
T
Rob_010101
  • Solved
VBA to turn a row into values only (based on list item)
Replies
3
Views
74
Peter_SSs
Peter_SSs
R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
197
Rokcmd
R
J
  • Question Question
Formatting error when switching from Mac to PC
Replies
6
Views
116
RoryA
RoryA
D
  • Solved
Calculating the difference between 2 time values using VBA
Replies
8
Views
144
Micron
M

Forum statistics

Threads
1,224,829
Messages
6,181,219
Members
453,024
Latest member
Wingit77

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