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 |
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>
Excel 2010 | |||
---|---|---|---|
B | |||
1 | 16600;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 | ||
2 | 16600 | ||
3 | 1426 | ||
4 | 3388 | ||
5 | 4773 | ||
6 | 9275 | ||
7 | 9610 | ||
8 | 20001 | ||
9 | 6162 | ||
10 | 20001 | ||
11 | 9610 | ||
12 | 1745 | ||
13 | 6121 | ||
14 | 6379 | ||
15 | 20001 | ||
16 | 20001 | ||
17 | 781 | ||
18 | 768 | ||
19 | 6908 | ||
20 | 9131 | ||
21 | 1426 | ||
22 | 1750 | ||
23 | 1782 | ||
24 | 1788 | ||
25 | 3253 | ||
26 | 3377 | ||
27 | 4773 | ||
28 | 9131 | ||
29 | 9431 | ||
30 | 20001 | ||
31 | 9094 | ||
32 | 3253 | ||
33 | 9610 | ||
34 | 6160 | ||
35 | 1750 | ||
36 | 1756 | ||
37 | 1766 | ||
38 | 1772 | ||
39 | 2310 | ||
40 | 6160 | ||
41 | 9882 | ||
42 | 9610 | ||
43 | 1322 | ||
44 | 1322 | ||
45 | 1422 | ||
46 | 2172 | ||
47 | 2462 | ||
48 | 17682 | ||
49 | 2310 | ||
50 | 9610 | ||
51 | 2310 | ||
52 | 9431 | ||
53 | 9610 | ||
54 | 9275 | ||
55 | 6177 | ||
56 | 6210 | ||
57 | 4809 | ||
58 | 1756 | ||
59 | 6121 | ||
60 | 1747 | ||
61 | 2310 | ||
62 | 1717 | ||
63 | 2287 | ||
64 | 1718 | ||
65 | 2493 | ||
66 | 6160 | ||
67 | 9610 | ||
68 | 1257 | ||
69 | 1766 | ||
70 | 2310 | ||
71 | 2329 | ||
72 | 9094 | ||
73 | 9114 | ||
74 | 9275 | ||
75 | 9610 | ||
76 | 10700 | ||
Sheet24 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | =IFERROR(MID(SUBSTITUTE(SUBSTITUTE(";"&SUBSTITUTE(B$1," ",""),";0",""),";",REPT(" ",255)),ROW(B1)*256,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 |
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 |
Excel 2010 | ||||||
---|---|---|---|---|---|---|
E | F | G | H | |||
1 | 99400;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;267850 | 492 | 489 | |||
2 | 99400 | |||||
3 | 72818 | |||||
4 | 432051 | |||||
5 | 341140 | |||||
6 | 156658 | |||||
7 | 21349 | |||||
8 | 90558 | |||||
9 | 291542 | |||||
10 | 90559 | |||||
11 | 21351 | |||||
12 | 323206 | |||||
13 | 38476 | |||||
14 | 69029 | |||||
15 | 90499 | |||||
16 | 90500 | |||||
17 | 180465 | |||||
18 | 2536143 | |||||
19 | 152115 | |||||
20 | 2101 | |||||
21 | 72817 | |||||
22 | 148564 | |||||
23 | 46005 | |||||
24 | 71131 | |||||
25 | 76255 | |||||
26 | 537828 | |||||
27 | 341139 | |||||
28 | 2102 | |||||
29 | 48416 | |||||
30 | 90557 | |||||
31 | 117015 | |||||
32 | 76256 | |||||
33 | 21346 | |||||
34 | 310013 | |||||
35 | 148565 | |||||
36 | 36700 | |||||
37 | 72521 | |||||
38 | 79088 | |||||
39 | 269249 | |||||
40 | 310014 | |||||
41 | 258293 | |||||
42 | 21350 | |||||
43 | 290246 | |||||
44 | 290247 | |||||
45 | 569446 | |||||
46 | 299638 | |||||
47 | 464381 | |||||
48 | 34900 | |||||
49 | 269247 | |||||
50 | 21347 | |||||
51 | 269248 | |||||
52 | 48417 | |||||
53 | 21348 | |||||
54 | 156659 | |||||
55 | 269967 | |||||
56 | 45128 | |||||
57 | 176777 | |||||
58 | 36701 | |||||
59 | 38477 | |||||
60 | 962050 | |||||
61 | 268633 | |||||
62 | 199591 | |||||
63 | 199092 | |||||
64 | 83285 | |||||
65 | 207751 | |||||
66 | 310012 | |||||
67 | 21345 | |||||
68 | 344018 | |||||
69 | 72522 | |||||
70 | 269250 | |||||
71 | 160797 | |||||
72 | 117016 | |||||
73 | 31810 | |||||
74 | 156660 | |||||
75 | 21352 | |||||
76 | 267850 | |||||
Sheet24 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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,"") |
Excel 2010 | ||||||
---|---|---|---|---|---|---|
E | F | G | H | |||
1 | 99400;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;267850 | 492 | 489 | |||
2 | 99400 | |||||
3 | 72818 | |||||
4 | 432051 | |||||
5 | 341140 | |||||
6 | 156658 | |||||
7 | 21349 | |||||
8 | 90558 | |||||
9 | 291542 | |||||
10 | 90559 | |||||
11 | 21351 | |||||
12 | 323206 | |||||
13 | 38476 | |||||
14 | 69029 | |||||
15 | 90499 | |||||
16 | 90500 | |||||
17 | 180465 | |||||
18 | 2536143 | |||||
19 | 152115 | |||||
20 | 2101 | |||||
21 | 72817 | |||||
22 | 148564 | |||||
23 | 46005 | |||||
24 | 71131 | |||||
25 | 76255 | |||||
26 | 537828 | |||||
27 | 341139 | |||||
28 | 2102 | |||||
29 | 48416 | |||||
30 | 90557 | |||||
31 | 117015 | |||||
32 | 76256 | |||||
33 | 21346 | |||||
34 | 310013 | |||||
35 | 148565 | |||||
36 | 36700 | |||||
37 | 72521 | |||||
38 | 79088 | |||||
39 | 269249 | |||||
40 | 310014 | |||||
41 | 258293 | |||||
42 | 21350 | |||||
43 | 290246 | |||||
44 | 290247 | |||||
45 | 569446 | |||||
46 | 299638 | |||||
47 | 464381 | |||||
48 | 34900 | |||||
49 | 269247 | |||||
50 | 21347 | |||||
51 | 269248 | |||||
52 | 48417 | |||||
53 | 21348 | |||||
54 | 156659 | |||||
55 | 269967 | |||||
56 | 45128 | |||||
57 | 176777 | |||||
58 | 36701 | |||||
59 | 38477 | |||||
60 | 962050 | |||||
61 | 268633 | |||||
62 | 199591 | |||||
63 | 199092 | |||||
64 | 83285 | |||||
65 | 207751 | |||||
66 | 310012 | |||||
67 | 21345 | |||||
68 | 344018 | |||||
69 | 72522 | |||||
70 | 269250 | |||||
71 | 160797 | |||||
72 | 117016 | |||||
73 | 31810 | |||||
74 | 156660 | |||||
75 | 21352 | |||||
76 | 267850 | |||||
Sheet24 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
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,"") |
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 | 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 | |
Acct | Tag# | |
9032 | 9655 | |
781 | 7459 | |
600 | 1469 | |
8 | 78545 | |
9 | 181359 | |
342298 | ||
6 | 218175 | |
23 | 311081 | |
943 | 270822 | |
9620 | 48886 | |
9810 | 5709 | |
1426 | 218176 | |
1426 | 72946 | |
3422 | 72947 | |
4773 | 12144 | |
16600 | 342299 | |
20001 | 101470 | |
4401 | 91151 | |
2306 | 74113 | |
6160 | 128672 | |
7667 | 311084 | |
9032 | 211718 | |
9781 | 9656 | |
2310 | 17460 | |
2148 | 270550 | |
520207 | 218074 | |
768 | 42849 | |
719 | 2547896 | |
1426 | 988579 | |
3422 | 72945 | |
6598 | 12143 | |
9431 | 104796 | |
20001 | 48885 | |
2197 | 91150 | |
9862 | 393008 | |
526416 | 160562 | |
9862 | 49587 | |
20001 | 160563 | |
9620 | 91152 | |
16600 | 5710 | |
1747 | 101471 | |
2306 | 969357 | |
20001 | 128654 | |
7106 | 91153 | |
2310 | 39379 | |
2310 | 270823 | |
1747 | 270824 | |
2329 | 969358 | |
16600 | 161095 | |
6160 | 101472 | |
6160 | 311082 | |
1257 | 311083 | |
1747 | 344909 | |
2329 | 969359 | |
161096 | ||
#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.