Splitting a cell by delimiter with formula. Warning: 312 splits needed for this.

[JakkeJakobsen]

Hi! I can't seem to find a way to split cells in Excel using formulas (I need it to be dynamic, so I can't use the built-in splitter).

What I have is up to 8 rows and 39 columns put into one cell. It's easy enough to get them spread out when I get them split up, but I can't split them. I can't find a working formula anywhere.

I usually work in Google Sheets, which has been way ahead of Excel for some years now, having stuff like =sort, which came to Excel last year or so, as well as =split, which splits by a delimiter, and it worked in my sheet. However, browser-based spreadsheets tend to get slow, mine is a professor in slow now, as I am using it to draw oil patterns for bowling lanes based on the input below. So I want an offline version that also works in Excel, as its a lot faster.

Sooo, any tips?

23 23 23 23 23 23 23 23 23 28 38 54 70 83 89 89 89 89 89 89 89 89 89 89 89 83 70 54 38 28 23 23 23 23 23 23 23 23 23 19 19 19 19 19 19 19 19 19 25 33 47 61 72 77 77 77 77 77 77 77 77 77 77 77 72 61 47 33 25 19 19 19 19 19 19 19 19 19 16 16 16 16 16 16 16 16 16 21 28 41 53 63 67 67 67 67 67 67 67 67 67 67 67 63 53 41 28 21 16 16 16 16 16 16 16 16 16 11 11 11 11 11 11 11 11 11 14 19 27 35 43 46 46 46 46 46 46 46 46 46 46 46 43 35 27 19 14 11 11 11 11 11 11 11 11 11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 <tbody> </tbody>

<tbody>
</tbody>

EDIT:
http://svolvarbowlingsenter.no/Patte...Norway2019.pdf

This is what I use this spreadsheet for, basically. This is done in Google Sheets, but I only lack this little split function from Google Sheets converted to Excel for it to work everywhere! :D

You can see the data I am looking to split in this PDF, top center, "Zone Oil Per Board Data". This was originally like the data in the table in my first post.

 

[pgc01]

Hi

Please clarify.

The numbers you posted do not match with the ones in the image.

To be clear,

- you have all the numbers you posted in just 1 cell separated by spaces
- you want to write each number in 1 cell, building a table with 39 columns

You posted 195 numbers in the cell and so this would result in a table 39 columns x 5 rows

Is that it?

 

[JakkeJakobsen]

Hi

Please clarify.

The numbers you posted do not match with the ones in the image.

To be clear,

- you have all the numbers you posted in just 1 cell separated by spaces
- you want to write each number in 1 cell, building a table with 39 columns

You posted 195 numbers in the cell and so this would result in a table 39 columns x 5 rows

Is that it?

Yes, it can be 39x3, or 39x8, it varies. But that shouldn't be a problem, if I do it cell by cell somehow, I just wrap everything in iferrors.
Main thing is to be able to split a cell with up to 312 numbers into up to 312 cells.

 

[Fluff]

Maybe

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th><th>Z</th><th>AA</th><th>AB</th><th>AC</th><th>AD</th><th>AE</th><th>AF</th><th>AG</th><th>AH</th><th>AI</th><th>AJ</th><th>AK</th><th>AL</th><th>AM</th><th>AN</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">23 23 23 23 23 23 23 23 23 28 38 54 70 83 89 89 89 89 89 89 89 89 89 89 89 83 70 54 38 28 23 23 23 23 23 23 23 23 23 19 19 19 19 19 19 19 19 19 25 33 47 61 72 77 77 77 77 77 77 77 77 77 77 77 72 61 47 33 25 19 19 19 19 19 19 19 19 19 16 16 16 16 16 16 16 16 16 21 28 41 53 63 67 67 67 67 67 67 67 67 67 67 67 63 53 41 28 21 16 16 16 16 16 16 16 16 16 11 11 11 11 11 11 11 11 11 14 19 27 35 43 46 46 46 46 46 46 46 46 46 46 46 43 35 27 19 14 11 11 11 11 11 11 11 11 11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">28</td><td style="text-align: right;;">38</td><td style="text-align: right;;">54</td><td style="text-align: right;;">70</td><td style="text-align: right;;">83</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">89</td><td style="text-align: right;;">83</td><td style="text-align: right;;">70</td><td style="text-align: right;;">54</td><td style="text-align: right;;">38</td><td style="text-align: right;;">28</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">25</td><td style="text-align: right;;">33</td><td style="text-align: right;;">47</td><td style="text-align: right;;">61</td><td style="text-align: right;;">72</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">77</td><td style="text-align: right;;">72</td><td style="text-align: right;;">61</td><td style="text-align: right;;">47</td><td style="text-align: right;;">33</td><td style="text-align: right;;">25</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">21</td><td style="text-align: right;;">28</td><td style="text-align: right;;">41</td><td style="text-align: right;;">53</td><td style="text-align: right;;">63</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">67</td><td style="text-align: right;;">63</td><td style="text-align: right;;">53</td><td style="text-align: right;;">41</td><td style="text-align: right;;">28</td><td style="text-align: right;;">21</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">14</td><td style="text-align: right;;">19</td><td style="text-align: right;;">27</td><td style="text-align: right;;">35</td><td style="text-align: right;;">43</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">46</td><td style="text-align: right;;">43</td><td style="text-align: right;;">35</td><td style="text-align: right;;">27</td><td style="text-align: right;;">19</td><td style="text-align: right;;">14</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:1.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">RC</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">$A$2,FIND(<font color="#FF00FF">"|",SUBSTITUTE(<font color="Navy">$A$2," ","|",COLUMN(<font color="Blue">A2</font>)+(<font color="Blue">ROW(<font color="Red">A1</font>)*39-39</font>)</font>)</font>)-1</font>)," ",REPT(<font color="Teal">" ",3</font>)</font>),3</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[JakkeJakobsen]

ADVERTISEMENT

Maybe

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
2	23 23 23 23 23 23 23 23 23 28 38 54 70 83 89 89 89 89 89 89 89 89 89 89 89 83 70 54 38 28 23 23 23 23 23 23 23 23 23 19 19 19 19 19 19 19 19 19 25 33 47 61 72 77 77 77 77 77 77 77 77 77 77 77 72 61 47 33 25 19 19 19 19 19 19 19 19 19 16 16 16 16 16 16 16 16 16 21 28 41 53 63 67 67 67 67 67 67 67 67 67 67 67 63 53 41 28 21 16 16 16 16 16 16 16 16 16 11 11 11 11 11 11 11 11 11 14 19 27 35 43 46 46 46 46 46 46 46 46 46 46 46 43 35 27 19 14 11 11 11 11 11 11 11 11 11 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0	23	23	23	23	23	23	23	23	23	28	38	54	70	83	89	89	89	89	89	89	89	89	89	89	89	83	70	54	38	28	23	23	23	23	23	23	23	23	23
3		19	19	19	19	19	19	19	19	19	25	33	47	61	72	77	77	77	77	77	77	77	77	77	77	77	72	61	47	33	25	19	19	19	19	19	19	19	19	19
4		16	16	16	16	16	16	16	16	16	21	28	41	53	63	67	67	67	67	67	67	67	67	67	67	67	63	53	41	28	21	16	16	16	16	16	16	16	16	16
5		11	11	11	11	11	11	11	11	11	14	19	27	35	43	46	46	46	46	46	46	46	46	46	46	46	43	35	27	19	14	11	11	11	11	11	11	11	11	11
6		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
7
8
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

RC​

Worksheet Formulas Cell Formula B2 =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($A$2,FIND("|",SUBSTITUTE($A$2," ","|",COLUMN(A2)+(ROW(A1)*39-39)))-1)," ",REPT(" ",3)),3)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

There is a zero lacking at the end, and its bugging with other values, like the ones below:

16 16 18 20 30 50 70 77 79 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 80 79 77 70 50 30 20 18 16 1613 13 14 15 16 22 28 35 42 49 56 59 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 59 56 45 35 25 18 15 14 13 12 1211 11 11 11 12 13 14 17 20 25 30 35 40 44 47 48 48 48 48 48 48 48 48 48 48 48 47 44 38 28 20 16 13 12 11 10 10 10 108 8 8 8 8 9 9 10 11 13 16 20 25 30 34 36 37 37 37 37 37 37 37 37 37 37 36 33 27 18 12 10 9 8 8 8 8 8 87 7 7 7 7 7 8 8 8 9 10 13 17 21 25 27 28 28 28 28 28 28 28 28 28 28 27 24 16 9 8 7 7 6 6 6 6 6 60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

<tbody>
</tbody>

It doesn't include the last row of only zeros at all in this one. Is there an upper limit you set that doesn't allow to go past X numbers?

 

[JakkeJakobsen]

The lacking row of numbers was my fault it seems. However, it still lacks the last zero for some odd reason.

 

[Fluff]

ADVERTISEMENT

You're 1st example only had 194 values rather than 195 and the data in post#5 only has 228 rather than 234.
This is why you are not getting a complete last row.

 

[JakkeJakobsen]

You're 1st example only had 194 values rather than 195 and the data in post#5 only has 228 rather than 234.
This is why you are not getting a complete last row.

No that can't be? I just copied the exact same numbers into Google Shets, and got 5x39 there with the first example. When I do the same in Excel with the exact same data, it removes the last number consistently.

Same numbers copied from a PDF into Google Sheets. https://www.dropbox.com/sh/tfevjrln...0&preview=BR2-ES.pdf&subfolder_nav_tracking=1

EDIT: Wrong PDF, it happens with that one too, but this is the one with the numbers I gave you in the first post: https://www.dropbox.com/sh/tfevjrln...&preview=BC5-R66.pdf&subfolder_nav_tracking=1

Do not copy from Dropbox if you wanna test that, it'll drop numbers then it seems. I have these locally on my computer.

Since it is always one number lacking in the end (no matter what the number is), I just cheated and had the same array below with the last column having an if formula copying the previous column if it was empty.

 

[Fluff]

You're quite, I wasn't thinking. The formula should be
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($A$2,FIND("|",SUBSTITUTE($A$2&" "," ","|",COLUMN(A2)+(ROW(A1)*39-39)))-1)," ",REPT(" ",3)),3)),"")

 

[JakkeJakobsen]

You're quite, I wasn't thinking. The formula should be
=IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT($A$2,FIND("|",SUBSTITUTE($A$2&" "," ","|",COLUMN(A2)+(ROW(A1)*39-39)))-1)," ",REPT(" ",3)),3)),"")

Thank you very much, it works perfectly! :D