{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Text to Columns","How It's made","TexttoCol-Consecutive Delimiter","Portable version","Text toCol using VBA"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Text to Columns","index":0,"n_rows":10,"n_cols":12,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Text to Columns Template - Single Delimiter"]
[null,null,null,null,null,null,null,null,null,null,null,"Created by Catalin Bombea"]
["Delimiter:","/"]
[]
["Paste Data starting in Cell A4:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6))-1),0))&$B$3)-1),\"\")","v":"2"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6))-1),0))&$B$3)-1),\"\")","v":"3"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6))-1),0))&$B$3)-1),\"\")","v":"7"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6))-1),0))&$B$3)-1),\"\")","v":"9"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6))-1),0))&$B$3)-1),\"\")","v":"10"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6))-1),0))&$B$3)-1),\"\")","v":""}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7))-1),0))&$B$3)-1),\"\")","v":"E"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7))-1),0))&$B$3)-1),\"\")","v":"F"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7))-1),0))&$B$3)-1),\"\")","v":"G"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7))-1),0))&$B$3)-1),\"\")","v":"H"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7))-1),0))&$B$3)-1),\"\")","v":"I"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7))-1),0))&$B$3)-1),\"\")","v":"J"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7))-1),0))&$B$3)-1),\"\")","v":""}]
["D/C/B/A/1",{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8))-1),0))&$B$3)-1),\"\")","v":""}]
["5/6/6/8/4",{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9))-1),0))&$B$3)-1),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday//Friday/Saturday",{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10))-1),0))&$B$3)-1),\"\")","v":"Sunday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10))-1),0))&$B$3)-1),\"\")","v":"Monday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10))-1),0))&$B$3)-1),\"\")","v":"Tuesday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10))-1),0))&$B$3)-1),\"\")","v":"Wednesday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10))-1),0))&$B$3)-1),\"\")","v":"Thursday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10))-1),0))&$B$3)-1),\"\")","v":"Friday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10))-1),0))&$B$3)-1),\"\")","v":"Saturday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10))-1),0))&$B$3)-1),\"\")","v":""}]

{"sheet":"How It's made","index":1,"n_rows":98,"n_cols":12,"rows_kept":98,"cut":false,"hidden":false,"merged":[[45,0,45,9],[17,0,17,11],[24,0,24,9],[25,0,25,9],[30,0,30,9],[33,0,33,9],[34,0,34,9],[35,0,35,9],[37,0,37,9],[41,0,41,9],[44,0,44,9],[52,0,52,9],[54,0,54,9],[56,0,56,9],[55,0,55,12],[58,0,58,9],[70,0,70,11],[81,0,81,11],[92,0,92,11],[95,0,95,11],[97,0,97,11]]}
["Text to Columns Template - Single Delimiter"]
["How It's Made:",null,null,null,null,null,null,null,null,null,null,"Created by Catalin Bombea"]
["Delimiter:","/"]
[]
["Paste Data starting in Cell A6:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6))-1),0))&$B$3)-1),\"\")","v":"2"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6))-1),0))&$B$3)-1),\"\")","v":"3"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6))-1),0))&$B$3)-1),\"\")","v":"7"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6))-1),0))&$B$3)-1),\"\")","v":"9"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6))-1),0))&$B$3)-1),\"\")","v":"10"},{"$f":"IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6))-1),0))&$B$3)-1),\"\")","v":""}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7))-1),0))&$B$3)-1),\"\")","v":"E"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7))-1),0))&$B$3)-1),\"\")","v":"F"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7))-1),0))&$B$3)-1),\"\")","v":"G"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7))-1),0))&$B$3)-1),\"\")","v":"H"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7))-1),0))&$B$3)-1),\"\")","v":"I"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7))-1),0))&$B$3)-1),\"\")","v":"J"},{"$f":"IFERROR(MID($A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7)),FIND($B$3,RIGHT($A7,LEN($A7)-IFERROR(FIND($B$3,$A7,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7))-1),0))&$B$3)-1),\"\")","v":""}]
["D/C/B/A/1",{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8)),FIND($B$3,RIGHT($A8,LEN($A8)-IFERROR(FIND($B$3,$A8,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8))-1),0))&$B$3)-1),\"\")","v":""}]
["5/6/6/8/4",{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9)),FIND($B$3,RIGHT($A9,LEN($A9)-IFERROR(FIND($B$3,$A9,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9))-1),0))&$B$3)-1),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10))-1),0))&$B$3)-1),\"\")","v":"Sunday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10))-1),0))&$B$3)-1),\"\")","v":"Monday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10))-1),0))&$B$3)-1),\"\")","v":"Tuesday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10))-1),0))&$B$3)-1),\"\")","v":"Wednesday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10))-1),0))&$B$3)-1),\"\")","v":"Thursday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10))-1),0))&$B$3)-1),\"\")","v":"Friday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10))-1),0))&$B$3)-1),\"\")","v":"Saturday"},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID($A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10)),FIND($B$3,RIGHT($A10,LEN($A10)-IFERROR(FIND($B$3,$A10,IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10))-1),0))&$B$3)-1),\"\")","v":""}]
[]
[]
[]
["Always start with a simple formula…","Using LEFT function",null,"Using MID function",null,"MID function arguments (i will refer to these later):"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"LEFT(A15,FIND($B$3,A15)-1)","v":"Sunday"},null,{"$f":"MID(A15,1,FIND($B$3,A15)-1)","v":"Sunday"},null,"MID(text,start_num,num_chars)"]
[]
["I will use MID from now on, it's more \"flexible\"..."]
["First value was easy… But for the next value, MID function start_num argument should not be 1, it should be the number corresponding to the first char after the first delimiter:"]
["Sunday/Monday/Tu…    M is is position 8, so this will be our value for the second argument: =MID(A20,8,FIND($B$3,A20)-1)"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A20,8,FIND($B$3,A20)-1)","v":"Monday"},"Success!"]
[]
["Will this work for all values? Let's try another, W from Wednesday is in position 23:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A23,23,FIND($B$3,A23)-1)","v":"Wednes"},"Looks like the result is wrong…"]
[]
["The reason for this error is simple: the formula for the num_chars argument of MID function, FIND($B$3,A23)-1 will ALWAYS return the same value (6) when evaluating cell A23"]
["Somehow, the FIND function should evaluate the rest of the string to find the NEXT delimiter! So, it should analyze a string without the first value, like: Monday/Tuesday/Wednesday/Thursday/Friday/Saturday; for the 3'rd value, FIND function should look into a string without the values previously extracted: Tuesday/Wednesday/Thursday/Friday/Saturday, and so on . How can we tell excel to look at the rest of the string, Instead of A23 from FIND($B$3,A23)-1?"]
[]
["Let’s look at the facts; the total number of characters in this string is 56 including delimiters (we can use =LEN(B30) to find out the number of characters in cell B30)."]
["For Monday we need to focus on the remaining 49, because the first value (Sunday) has 6 chars, and there is a delimiter also, 56-7 will be 49."]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A30,1,FIND($B$3,A30)-1)","v":"Sunday"},{"$f":"MID(A30,8,FIND($B$3,A30,8)-LEN(B30)-2)","v":"Monday"},"That is working!"]
["Let's compare the formulas for the first and second value:"]
["In cell B30, we have: =MID(A30,1,FIND($B$3,A30)-1) Result: Sunday"]
["In cell C30, we have:'=MID(A30,8,FIND($B$3,A30,8)-LEN(B29)-2) Result: Monday"]
["There is something new in the FIND function, I used the 3'rd argument (start_num), which is optional, (the default value is 1), and the value is exactly the same as MID's second argument!"]
["Another change is the use of LEN(B30) in the num_chars argument of MID function. The result of FIND($B$3,A30,8) is 14; this means that the function returns the position of the second delimiter from the entire initial string, including the first value and the first delimiter! LEN(B30) is 6, in this case. \"Monday\" has also 6 chars, but, until now, FIND($B$3,A30,8)-LEN(B30) will result in 14-6=8. In cell B30, we had to exclude 1 delimiter, but for the next cell, we have to exclude 2 delimiters. That's the reason for -1 and -2 in these 2 cells."]
["Now, we have to identify the vulnerable spots of the formula, we need to have dynamic formulas that will autoadjust. These spots are:\n=MID(A30,8,FIND($B$3,A30,8)-LEN(B30)-2)"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A37,1,FIND($B$3,A37)-1)","v":"Sunday"},{"$f":"MID(A37,8,FIND($B$3,A37,8)-LEN(B37)-2)","v":"Monday"},{"$f":"MID(A37,15,FIND($B$3,A37,15)-SUMPRODUCT(LEN(B37:C37))-3)","v":"Tuesday"}]
["For the 3'rd value from this string, which starts at position 15, we have to find the length of the first 2 values extracted already in cells B36 and C36. SUMPRODUCT will \"force\" LEN function to count the length of all cells from that range: =MID(A37,15,FIND($B$3,A37,15)-SUMPRODUCT(LEN(B37:C37))-3) . Let's fix now the last adjustment, which now it has the value of 3 (delimiters to exclude). One way, (from many), is to use COLUMN() function to create a dynamic When copied to the right, across columns:"]
["The formula =COLUMN()-1 will provide our dynamic adjustment:",{"$f":"COLUMN()-1","v":1},{"$f":"COLUMN()-1","v":2},3]
["Let's see again the results with this dynamic adjustment:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A41,1,FIND($B$3,A41)-(COLUMN()-1))","v":"Sunday"},{"$f":"MID(A41,8,FIND($B$3,A41,8)-SUMPRODUCT(LEN($B41:B41))-(COLUMN()-1))","v":"Monday"},{"$f":"MID(A41,15,FIND($B$3,A41,15)-SUMPRODUCT(LEN($B41:C41))-(COLUMN()-1))","v":"Tuesday"},"Works fine, let's move on…"]
["SUMPRODUCT(LEN(B41:C41)) looks great, but it's not auto adjusting when copied to the right, we have to do a little trick, setting the absolute references:"]
[]
[null,null,{"$f":"SUMPRODUCT(LEN($B41:B41))","v":6},{"$f":"SUMPRODUCT(LEN($B41:C41))","v":12},{"$f":"SUMPRODUCT(LEN($B41:D41))","v":19},"This will dynamically count the length of previous cells text"]
["$B40:B40 Explained: the dollar sign is used to lock only the column of the first cell in range, to allow the function to be copied down as well (row number will change)"]
["At this moment, our formula: =MID(A40,15,FIND($B$3,A40,15)-SUMPRODUCT(LEN($B40:C40))-(COLUMN()-1)) needs a dynamic input instead of a fixed value in red. Surprise :-), we already have a dynamic solution, look at cells D38 and D43, if you add them, they will be exactly what we need:  SUMPRODUCT(LEN($B40:C40))+(COLUMN()-1). It's very similar to the part colored in green, but here we need to add them: Let's replace 15 with this dynamic formula in cell D47:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"MID(A47,1,FIND($B$3,A47)-(COLUMN()-1))","v":"Sunday"},{"$f":"MID($A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:B47))-(COLUMN()-1))","v":"Monday"},{"$f":"MID($A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:C47))-(COLUMN()-1))","v":"Tuesday"}]
[]
[null,null,{"$f":"MID($A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:B47))-(COLUMN()-1))","v":"Monday"},{"$f":"MID($A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:C47))-(COLUMN()-1))","v":"Tuesday"},"Copying the cell D48 to the LEFT, over C49, will work fine."]
[]
[null,{"$f":"MID($A47,SUMPRODUCT(LEN(A47:$B47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN(A47:$B47))+(COLUMN()-1))-SUMPRODUCT(LEN(A47:$B47))-(COLUMN()-1))","v":{"$error":"#VALUE!"}},{"$f":"MID($A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:B47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:B47))-(COLUMN()-1))","v":"Monday"},{"$f":"MID($A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1),FIND($B$3,$A47,SUMPRODUCT(LEN($B47:C47))+(COLUMN()-1))-SUMPRODUCT(LEN($B47:C47))-(COLUMN()-1))","v":"Tuesday"}]
[]
["Let's check the column number  where the formula returns error:"]
["=COLUMN():",{"$f":"COLUMN()","v":2},{"$f":"COLUMN()","v":3},{"$f":"COLUMN()","v":4},{"$f":"COLUMN()","v":5}]
["Review: the formula works in all cells, except column 2… Let's use both formulas then, if column number is 2, we use the first simple formula for the first value: LEFT($A58,FIND($B$3,$A58)-1); for all other columns, we can use the other formula:"]
["=IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN(A58:$B58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN(A58:$B58))+(COLUMN()-1))-SUMPRODUCT(LEN(A58:$B58))-(COLUMN()-1)))"]
["Translation: IF column number is 2, use green formula, else-use blue formula. And let's check it for the entire range:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN(A58:$B58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN(A58:$B58))+(COLUMN()-1))-SUMPRODUCT(LEN(A58:$B58))-(COLUMN()-1)))","v":"Sunday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN(B58:$B58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN(B58:$B58))+(COLUMN()-1))-SUMPRODUCT(LEN(B58:$B58))-(COLUMN()-1)))","v":"Monday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:C58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:C58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:C58))-(COLUMN()-1)))","v":"Tuesday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:D58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:D58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:D58))-(COLUMN()-1)))","v":"Wednesday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:E58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:E58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:E58))-(COLUMN()-1)))","v":"Thursday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:F58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:F58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:F58))-(COLUMN()-1)))","v":"Friday"},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:G58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:G58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:G58))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:H58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:H58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:H58))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A58,FIND($B$3,$A58)-1),MID($A58,SUMPRODUCT(LEN($B58:I58))+(COLUMN()-1),FIND($B$3,$A58,SUMPRODUCT(LEN($B58:I58))+(COLUMN()-1))-SUMPRODUCT(LEN($B58:I58))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
["After all elements are returned, the formula returns an error. An IFERROR wrapped around our formula will take care of this minor problem:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN(A60:$B60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN(A60:$B60))+(COLUMN()-1))-SUMPRODUCT(LEN(A60:$B60))-(COLUMN()-1))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN(B60:$B60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN(B60:$B60))+(COLUMN()-1))-SUMPRODUCT(LEN(B60:$B60))-(COLUMN()-1))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:C60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:C60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:C60))-(COLUMN()-1))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:D60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:D60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:D60))-(COLUMN()-1))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:E60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:E60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:E60))-(COLUMN()-1))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:F60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:F60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:F60))-(COLUMN()-1))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:G60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:G60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:G60))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:H60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:H60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:H60))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A60,FIND($B$3,$A60)-1),MID($A60,SUMPRODUCT(LEN($B60:I60))+(COLUMN()-1),FIND($B$3,$A60,SUMPRODUCT(LEN($B60:I60))+(COLUMN()-1))-SUMPRODUCT(LEN($B60:I60))-(COLUMN()-1))),\"\")","v":""}]
[]
["Will this formula work and adjust itself across rows and columns? Let' try this:"]
[]
["Paste Data starting in Cell A6:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN(A65:$B65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN(A65:$B65))+(COLUMN()-1))-SUMPRODUCT(LEN(A65:$B65))-(COLUMN()-1))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN(B65:$B65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN(B65:$B65))+(COLUMN()-1))-SUMPRODUCT(LEN(B65:$B65))-(COLUMN()-1))),\"\")","v":"2"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:C65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:C65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:C65))-(COLUMN()-1))),\"\")","v":"3"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:D65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:D65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:D65))-(COLUMN()-1))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:E65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:E65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:E65))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:F65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:F65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:F65))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:G65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:G65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:G65))-(COLUMN()-1))),\"\")","v":"7"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:H65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:H65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:H65))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:I65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:I65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:I65))-(COLUMN()-1))),\"\")","v":"9"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:J65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:J65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:J65))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A65,FIND($B$3,$A65)-1),MID($A65,SUMPRODUCT(LEN($B65:K65))+(COLUMN()-1),FIND($B$3,$A65,SUMPRODUCT(LEN($B65:K65))+(COLUMN()-1))-SUMPRODUCT(LEN($B65:K65))-(COLUMN()-1))),\"\")","v":""}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN(A66:$B66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN(A66:$B66))+(COLUMN()-1))-SUMPRODUCT(LEN(A66:$B66))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN(B66:$B66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN(B66:$B66))+(COLUMN()-1))-SUMPRODUCT(LEN(B66:$B66))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:C66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:C66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:C66))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:D66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:D66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:D66))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:E66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:E66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:E66))-(COLUMN()-1))),\"\")","v":"E"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:F66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:F66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:F66))-(COLUMN()-1))),\"\")","v":"F"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:G66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:G66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:G66))-(COLUMN()-1))),\"\")","v":"G"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:H66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:H66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:H66))-(COLUMN()-1))),\"\")","v":"H"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:I66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:I66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:I66))-(COLUMN()-1))),\"\")","v":"I"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:J66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:J66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:J66))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A66,FIND($B$3,$A66)-1),MID($A66,SUMPRODUCT(LEN($B66:K66))+(COLUMN()-1),FIND($B$3,$A66,SUMPRODUCT(LEN($B66:K66))+(COLUMN()-1))-SUMPRODUCT(LEN($B66:K66))-(COLUMN()-1))),\"\")","v":""}]
["D/C/B/A/1",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN(A67:$B67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN(A67:$B67))+(COLUMN()-1))-SUMPRODUCT(LEN(A67:$B67))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN(B67:$B67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN(B67:$B67))+(COLUMN()-1))-SUMPRODUCT(LEN(B67:$B67))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:C67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:C67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:C67))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:D67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:D67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:D67))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:E67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:E67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:E67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:F67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:F67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:F67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:G67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:G67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:G67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:H67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:H67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:H67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:I67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:I67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:I67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:J67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:J67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:J67))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A67,FIND($B$3,$A67)-1),MID($A67,SUMPRODUCT(LEN($B67:K67))+(COLUMN()-1),FIND($B$3,$A67,SUMPRODUCT(LEN($B67:K67))+(COLUMN()-1))-SUMPRODUCT(LEN($B67:K67))-(COLUMN()-1))),\"\")","v":""}]
["5/6/6/8/4",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN(A68:$B68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN(A68:$B68))+(COLUMN()-1))-SUMPRODUCT(LEN(A68:$B68))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN(B68:$B68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN(B68:$B68))+(COLUMN()-1))-SUMPRODUCT(LEN(B68:$B68))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:C68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:C68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:C68))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:D68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:D68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:D68))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:E68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:E68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:E68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:F68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:F68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:F68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:G68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:G68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:G68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:H68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:H68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:H68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:I68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:I68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:I68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:J68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:J68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:J68))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A68,FIND($B$3,$A68)-1),MID($A68,SUMPRODUCT(LEN($B68:K68))+(COLUMN()-1),FIND($B$3,$A68,SUMPRODUCT(LEN($B68:K68))+(COLUMN()-1))-SUMPRODUCT(LEN($B68:K68))-(COLUMN()-1))),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN(A69:$B69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN(A69:$B69))+(COLUMN()-1))-SUMPRODUCT(LEN(A69:$B69))-(COLUMN()-1))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN(B69:$B69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN(B69:$B69))+(COLUMN()-1))-SUMPRODUCT(LEN(B69:$B69))-(COLUMN()-1))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:C69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:C69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:C69))-(COLUMN()-1))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:D69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:D69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:D69))-(COLUMN()-1))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:E69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:E69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:E69))-(COLUMN()-1))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:F69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:F69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:F69))-(COLUMN()-1))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:G69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:G69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:G69))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:H69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:H69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:H69))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:I69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:I69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:I69))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:J69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:J69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:J69))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A69,FIND($B$3,$A69)-1),MID($A69,SUMPRODUCT(LEN($B69:K69))+(COLUMN()-1),FIND($B$3,$A69,SUMPRODUCT(LEN($B69:K69))+(COLUMN()-1))-SUMPRODUCT(LEN($B69:K69))-(COLUMN()-1))),\"\")","v":""}]
[]
["Oops: The last value is not extracted! Why? Because at the last element, FIND function will try to find the next delimiter, but the last value has no delimiter after it!"]
["=FIND($B$3,$A75,SUMPRODUCT(LEN(B75:$B75))+(COLUMN()-1)) will return:",null,{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN(B75:$B75))+(COLUMN()-1))","v":4},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:C75))+(COLUMN()-1))","v":6},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:D75))+(COLUMN()-1))","v":8},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:E75))+(COLUMN()-1))","v":10},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:F75))+(COLUMN()-1))","v":12},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:G75))+(COLUMN()-1))","v":14},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:H75))+(COLUMN()-1))","v":16},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:I75))+(COLUMN()-1))","v":18},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:J75))+(COLUMN()-1))","v":{"$error":"#VALUE!"}},{"$f":"FIND($B$3,$A75,SUMPRODUCT(LEN($B75:K75))+(COLUMN()-1))","v":{"$error":"#VALUE!"}}]
["Remove the IFERROR to see what happens:"]
["Paste Data starting in Cell A6:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN(A75:$B75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN(A75:$B75))+(COLUMN()-1))-SUMPRODUCT(LEN(A75:$B75))-(COLUMN()-1)))","v":"1"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN(B75:$B75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN(B75:$B75))+(COLUMN()-1))-SUMPRODUCT(LEN(B75:$B75))-(COLUMN()-1)))","v":"2"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:C75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:C75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:C75))-(COLUMN()-1)))","v":"3"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:D75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:D75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:D75))-(COLUMN()-1)))","v":"4"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:E75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:E75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:E75))-(COLUMN()-1)))","v":"5"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:F75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:F75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:F75))-(COLUMN()-1)))","v":"6"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:G75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:G75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:G75))-(COLUMN()-1)))","v":"7"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:H75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:H75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:H75))-(COLUMN()-1)))","v":"8"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:I75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:I75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:I75))-(COLUMN()-1)))","v":"9"},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:J75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:J75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:J75))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A75,FIND($B$3,$A75)-1),MID($A75,SUMPRODUCT(LEN($B75:K75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN($B75:K75))+(COLUMN()-1))-SUMPRODUCT(LEN($B75:K75))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN(A76:$B76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN(A76:$B76))+(COLUMN()-1))-SUMPRODUCT(LEN(A76:$B76))-(COLUMN()-1)))","v":"A"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN(B76:$B76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN(B76:$B76))+(COLUMN()-1))-SUMPRODUCT(LEN(B76:$B76))-(COLUMN()-1)))","v":"B"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:C76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:C76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:C76))-(COLUMN()-1)))","v":"C"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:D76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:D76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:D76))-(COLUMN()-1)))","v":"D"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:E76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:E76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:E76))-(COLUMN()-1)))","v":"E"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:F76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:F76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:F76))-(COLUMN()-1)))","v":"F"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:G76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:G76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:G76))-(COLUMN()-1)))","v":"G"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:H76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:H76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:H76))-(COLUMN()-1)))","v":"H"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:I76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:I76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:I76))-(COLUMN()-1)))","v":"I"},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:J76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:J76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:J76))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A76,FIND($B$3,$A76)-1),MID($A76,SUMPRODUCT(LEN($B76:K76))+(COLUMN()-1),FIND($B$3,$A76,SUMPRODUCT(LEN($B76:K76))+(COLUMN()-1))-SUMPRODUCT(LEN($B76:K76))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
["D/C/B/A/1",{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN(A77:$B77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN(A77:$B77))+(COLUMN()-1))-SUMPRODUCT(LEN(A77:$B77))-(COLUMN()-1)))","v":"D"},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN(B77:$B77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN(B77:$B77))+(COLUMN()-1))-SUMPRODUCT(LEN(B77:$B77))-(COLUMN()-1)))","v":"C"},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:C77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:C77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:C77))-(COLUMN()-1)))","v":"B"},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:D77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:D77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:D77))-(COLUMN()-1)))","v":"A"},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:E77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:E77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:E77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:F77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:F77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:F77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:G77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:G77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:G77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:H77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:H77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:H77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:I77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:I77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:I77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:J77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:J77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:J77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A77,FIND($B$3,$A77)-1),MID($A77,SUMPRODUCT(LEN($B77:K77))+(COLUMN()-1),FIND($B$3,$A77,SUMPRODUCT(LEN($B77:K77))+(COLUMN()-1))-SUMPRODUCT(LEN($B77:K77))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
["5/6/6/8/4",{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN(A78:$B78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN(A78:$B78))+(COLUMN()-1))-SUMPRODUCT(LEN(A78:$B78))-(COLUMN()-1)))","v":"5"},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN(B78:$B78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN(B78:$B78))+(COLUMN()-1))-SUMPRODUCT(LEN(B78:$B78))-(COLUMN()-1)))","v":"6"},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:C78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:C78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:C78))-(COLUMN()-1)))","v":"6"},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:D78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:D78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:D78))-(COLUMN()-1)))","v":"8"},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:E78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:E78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:E78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:F78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:F78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:F78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:G78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:G78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:G78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:H78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:H78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:H78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:I78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:I78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:I78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:J78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:J78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:J78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A78,FIND($B$3,$A78)-1),MID($A78,SUMPRODUCT(LEN($B78:K78))+(COLUMN()-1),FIND($B$3,$A78,SUMPRODUCT(LEN($B78:K78))+(COLUMN()-1))-SUMPRODUCT(LEN($B78:K78))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN(A79:$B79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN(A79:$B79))+(COLUMN()-1))-SUMPRODUCT(LEN(A79:$B79))-(COLUMN()-1)))","v":"Sunday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN(B79:$B79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN(B79:$B79))+(COLUMN()-1))-SUMPRODUCT(LEN(B79:$B79))-(COLUMN()-1)))","v":"Monday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:C79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:C79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:C79))-(COLUMN()-1)))","v":"Tuesday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:D79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:D79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:D79))-(COLUMN()-1)))","v":"Wednesday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:E79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:E79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:E79))-(COLUMN()-1)))","v":"Thursday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:F79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:F79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:F79))-(COLUMN()-1)))","v":"Friday"},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:G79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:G79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:G79))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:H79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:H79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:H79))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:I79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:I79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:I79))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:J79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:J79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:J79))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}},{"$f":"IF(COLUMN()=2,LEFT($A79,FIND($B$3,$A79)-1),MID($A79,SUMPRODUCT(LEN($B79:K79))+(COLUMN()-1),FIND($B$3,$A79,SUMPRODUCT(LEN($B79:K79))+(COLUMN()-1))-SUMPRODUCT(LEN($B79:K79))-(COLUMN()-1)))","v":{"$error":"#VALUE!"}}]
[]
["The \"Fix\" is simple, add a \"virtual\" delimiter after the  initial string in the blue formula:"]
["MID($A75,SUMPRODUCT(LEN(A75:$B75))+(COLUMN()-1),FIND($B$3,$A75,SUMPRODUCT(LEN(A75:$B75))+(COLUMN()-1))-SUMPRODUCT(LEN(A75:$B75))-(COLUMN()-1))"]
["Instead of: $A75, change to: $A75&$B$3 , we get the last match too:",null,{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN(B75:$B75))+(COLUMN()-1))","v":4},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:C75))+(COLUMN()-1))","v":6},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:D75))+(COLUMN()-1))","v":8},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:E75))+(COLUMN()-1))","v":10},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:F75))+(COLUMN()-1))","v":12},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:G75))+(COLUMN()-1))","v":14},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:H75))+(COLUMN()-1))","v":16},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:I75))+(COLUMN()-1))","v":18},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:J75))+(COLUMN()-1))","v":21},{"$f":"FIND($B$3,$A75&$B$3,SUMPRODUCT(LEN($B75:K75))+(COLUMN()-1))","v":{"$error":"#VALUE!"}}]
["Wrap IFERROR around the formula, and it works:"]
["Paste Data starting in Cell A6:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN(A86:$B86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN(A86:$B86))+(COLUMN()-1))-SUMPRODUCT(LEN(A86:$B86))-(COLUMN()-1))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN(B86:$B86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN(B86:$B86))+(COLUMN()-1))-SUMPRODUCT(LEN(B86:$B86))-(COLUMN()-1))),\"\")","v":"2"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:C86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:C86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:C86))-(COLUMN()-1))),\"\")","v":"3"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:D86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:D86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:D86))-(COLUMN()-1))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:E86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:E86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:E86))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:F86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:F86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:F86))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:G86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:G86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:G86))-(COLUMN()-1))),\"\")","v":"7"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:H86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:H86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:H86))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:I86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:I86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:I86))-(COLUMN()-1))),\"\")","v":"9"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:J86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:J86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:J86))-(COLUMN()-1))),\"\")","v":"10"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN($B86:K86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN($B86:K86))+(COLUMN()-1))-SUMPRODUCT(LEN($B86:K86))-(COLUMN()-1))),\"\")","v":""}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN(A87:$B87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN(A87:$B87))+(COLUMN()-1))-SUMPRODUCT(LEN(A87:$B87))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN(B87:$B87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN(B87:$B87))+(COLUMN()-1))-SUMPRODUCT(LEN(B87:$B87))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:C87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:C87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:C87))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:D87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:D87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:D87))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:E87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:E87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:E87))-(COLUMN()-1))),\"\")","v":"E"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:F87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:F87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:F87))-(COLUMN()-1))),\"\")","v":"F"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:G87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:G87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:G87))-(COLUMN()-1))),\"\")","v":"G"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:H87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:H87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:H87))-(COLUMN()-1))),\"\")","v":"H"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:I87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:I87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:I87))-(COLUMN()-1))),\"\")","v":"I"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:J87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:J87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:J87))-(COLUMN()-1))),\"\")","v":"J"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A87,FIND($B$3,$A87)-1),MID($A87,SUMPRODUCT(LEN($B87:K87))+(COLUMN()-1),FIND($B$3,$A87&$B$3,SUMPRODUCT(LEN($B87:K87))+(COLUMN()-1))-SUMPRODUCT(LEN($B87:K87))-(COLUMN()-1))),\"\")","v":""}]
["D/C/B/A/1",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN(A88:$B88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN(A88:$B88))+(COLUMN()-1))-SUMPRODUCT(LEN(A88:$B88))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN(B88:$B88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN(B88:$B88))+(COLUMN()-1))-SUMPRODUCT(LEN(B88:$B88))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:C88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:C88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:C88))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:D88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:D88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:D88))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:E88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:E88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:E88))-(COLUMN()-1))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:F88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:F88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:F88))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:G88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:G88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:G88))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:H88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:H88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:H88))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:I88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:I88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:I88))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:J88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:J88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:J88))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A88,FIND($B$3,$A88)-1),MID($A88,SUMPRODUCT(LEN($B88:K88))+(COLUMN()-1),FIND($B$3,$A88&$B$3,SUMPRODUCT(LEN($B88:K88))+(COLUMN()-1))-SUMPRODUCT(LEN($B88:K88))-(COLUMN()-1))),\"\")","v":""}]
["5/6/6/8/4",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN(A89:$B89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN(A89:$B89))+(COLUMN()-1))-SUMPRODUCT(LEN(A89:$B89))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN(B89:$B89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN(B89:$B89))+(COLUMN()-1))-SUMPRODUCT(LEN(B89:$B89))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:C89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:C89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:C89))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:D89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:D89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:D89))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:E89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:E89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:E89))-(COLUMN()-1))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:F89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:F89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:F89))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:G89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:G89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:G89))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:H89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:H89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:H89))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:I89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:I89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:I89))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:J89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:J89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:J89))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A89,FIND($B$3,$A89)-1),MID($A89,SUMPRODUCT(LEN($B89:K89))+(COLUMN()-1),FIND($B$3,$A89&$B$3,SUMPRODUCT(LEN($B89:K89))+(COLUMN()-1))-SUMPRODUCT(LEN($B89:K89))-(COLUMN()-1))),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN(A90:$B90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN(A90:$B90))+(COLUMN()-1))-SUMPRODUCT(LEN(A90:$B90))-(COLUMN()-1))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN(B90:$B90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN(B90:$B90))+(COLUMN()-1))-SUMPRODUCT(LEN(B90:$B90))-(COLUMN()-1))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:C90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:C90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:C90))-(COLUMN()-1))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:D90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:D90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:D90))-(COLUMN()-1))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:E90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:E90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:E90))-(COLUMN()-1))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:F90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:F90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:F90))-(COLUMN()-1))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:G90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:G90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:G90))-(COLUMN()-1))),\"\")","v":"Saturday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:H90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:H90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:H90))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:I90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:I90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:I90))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:J90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:J90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:J90))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A90,FIND($B$3,$A90)-1),MID($A90,SUMPRODUCT(LEN($B90:K90))+(COLUMN()-1),FIND($B$3,$A90&$B$3,SUMPRODUCT(LEN($B90:K90))+(COLUMN()-1))-SUMPRODUCT(LEN($B90:K90))-(COLUMN()-1))),\"\")","v":""}]
[]
["FINALLY, our formula:"]
["=IFERROR(IF(COLUMN()=2,LEFT($A86,FIND($B$3,$A86)-1),MID($A86,SUMPRODUCT(LEN(A86:$B86))+(COLUMN()-1),FIND($B$3,$A86&$B$3,SUMPRODUCT(LEN(A86:$B86))+(COLUMN()-1))-SUMPRODUCT(LEN(A86:$B86))-(COLUMN()-1))),\"\")"]
[]
["If you look closer, this formula is slightly different from the initial formula, from top of this page:"]
["=IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")"]
[]
["Why? Because there are many roads to Rome… (There are many ways to get the same result :) )"]

{"sheet":"TexttoCol-Consecutive Delimiter","index":2,"n_rows":21,"n_cols":12,"rows_kept":21,"cut":false,"hidden":false,"merged":[[12,0,12,11],[13,0,13,11],[15,0,15,11],[18,0,18,11],[20,0,20,11],[16,0,16,11],[17,0,17,11],[19,0,19,11]]}
["Text to Columns Template - Treat Consecutive Delimiters as one"]
[null,null,null,null,null,null,null,null,null,null,null,"Created by Catalin Bombea"]
["Delimiter:","/"]
[]
["Data","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3//4/5/6//7/8//9/10",{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B6:$B6))+COLUMN(B6))-1),0))&$B$3)-1),\"\")","v":"2"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:C6))+COLUMN(C6))-1),0))&$B$3)-1),\"\")","v":"3"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:D6))+COLUMN(D6))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:E6))+COLUMN(E6))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:F6))+COLUMN(F6))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:G6))+COLUMN(G6))-1),0))&$B$3)-1),\"\")","v":"7"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:H6))+COLUMN(H6))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:I6))+COLUMN(I6))-1),0))&$B$3)-1),\"\")","v":"9"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:J6))+COLUMN(J6))-1),0))&$B$3)-1),\"\")","v":"10"},{"$f":"IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B6:K6))+COLUMN(K6))-1),0))&$B$3)-1),\"\")","v":""}]
["A/B/C//D/E/F//G/H/I/J",{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A7:$B7))+COLUMN(A7))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B7:$B7))+COLUMN(B7))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:C7))+COLUMN(C7))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:D7))+COLUMN(D7))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:E7))+COLUMN(E7))-1),0))&$B$3)-1),\"\")","v":"E"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:F7))+COLUMN(F7))-1),0))&$B$3)-1),\"\")","v":"F"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:G7))+COLUMN(G7))-1),0))&$B$3)-1),\"\")","v":"G"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:H7))+COLUMN(H7))-1),0))&$B$3)-1),\"\")","v":"H"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:I7))+COLUMN(I7))-1),0))&$B$3)-1),\"\")","v":"I"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:J7))+COLUMN(J7))-1),0))&$B$3)-1),\"\")","v":"J"},{"$f":"IFERROR(MID(SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7)),FIND($B$3,RIGHT(SUBSTITUTE($A7,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A7,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A7,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B7:K7))+COLUMN(K7))-1),0))&$B$3)-1),\"\")","v":""}]
["D/C/B//A/1",{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A8:$B8))+COLUMN(A8))-1),0))&$B$3)-1),\"\")","v":"D"},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B8:$B8))+COLUMN(B8))-1),0))&$B$3)-1),\"\")","v":"C"},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:C8))+COLUMN(C8))-1),0))&$B$3)-1),\"\")","v":"B"},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:D8))+COLUMN(D8))-1),0))&$B$3)-1),\"\")","v":"A"},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:E8))+COLUMN(E8))-1),0))&$B$3)-1),\"\")","v":"1"},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:F8))+COLUMN(F8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:G8))+COLUMN(G8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:H8))+COLUMN(H8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:I8))+COLUMN(I8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:J8))+COLUMN(J8))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8)),FIND($B$3,RIGHT(SUBSTITUTE($A8,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A8,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A8,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B8:K8))+COLUMN(K8))-1),0))&$B$3)-1),\"\")","v":""}]
["5/6//6/8/4",{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A9:$B9))+COLUMN(A9))-1),0))&$B$3)-1),\"\")","v":"5"},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B9:$B9))+COLUMN(B9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:C9))+COLUMN(C9))-1),0))&$B$3)-1),\"\")","v":"6"},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:D9))+COLUMN(D9))-1),0))&$B$3)-1),\"\")","v":"8"},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:E9))+COLUMN(E9))-1),0))&$B$3)-1),\"\")","v":"4"},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:F9))+COLUMN(F9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:G9))+COLUMN(G9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:H9))+COLUMN(H9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:I9))+COLUMN(I9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:J9))+COLUMN(J9))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9)),FIND($B$3,RIGHT(SUBSTITUTE($A9,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A9,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A9,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B9:K9))+COLUMN(K9))-1),0))&$B$3)-1),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday//Saturday",{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A10:$B10))+COLUMN(A10))-1),0))&$B$3)-1),\"\")","v":"Sunday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(B10:$B10))+COLUMN(B10))-1),0))&$B$3)-1),\"\")","v":"Monday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:C10))+COLUMN(C10))-1),0))&$B$3)-1),\"\")","v":"Tuesday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:D10))+COLUMN(D10))-1),0))&$B$3)-1),\"\")","v":"Wednesday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:E10))+COLUMN(E10))-1),0))&$B$3)-1),\"\")","v":"Thursday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:F10))+COLUMN(F10))-1),0))&$B$3)-1),\"\")","v":"Friday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:G10))+COLUMN(G10))-1),0))&$B$3)-1),\"\")","v":"Saturday"},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:H10))+COLUMN(H10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:I10))+COLUMN(I10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:J10))+COLUMN(J10))-1),0))&$B$3)-1),\"\")","v":""},{"$f":"IFERROR(MID(SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10)),FIND($B$3,RIGHT(SUBSTITUTE($A10,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A10,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A10,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN($B10:K10))+COLUMN(K10))-1),0))&$B$3)-1),\"\")","v":""}]
[]
[]
["This version will treat consecutive delimiters from the text string as one delimiter."]
["The reason for this version is that data may have 2 (maybe more) consecutive spaces, or commas, this leads to gaps between values, like in sheet 'Text to Columns', cell A10. Cell G10 is the consequence of the existence of 2 consecutive delimiters in the text string."]
[]
["The formula: SUBSTITUTE($A6,$B$3&$B$3,$B$3) will replace each pair of consecutive delimiters with a single delimiter. "]
[{"$f":"A6&\" will be changed to: \"&SUBSTITUTE($A6,$B$3&$B$3,$B$3)","v":"1/2/3//4/5/6//7/8//9/10 will be changed to: 1/2/3/4/5/6/7/8/9/10"}]
["The above SUBSTITUTE formula will replace the reference to $A6 from initial formula:"]
["=IFERROR(MID($A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT($A6,LEN($A6)-IFERROR(FIND($B$3,$A6,IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")"]
["Replace: $A6 with SUBSTITUTE($A6,$B$3&$B$3,$B$3) in all 4 instances:"]
["=IFERROR(MID(SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6)),FIND($B$3,RIGHT(SUBSTITUTE($A6,$B$3&$B$3,$B$3),LEN(SUBSTITUTE($A6,$B$3&$B$3,$B$3))-IFERROR(FIND($B$3,SUBSTITUTE($A6,$B$3&$B$3,$B$3),IF(COLUMN()=2,1,SUMPRODUCT(LEN(A6:$B6))+COLUMN(A6))-1),0))&$B$3)-1),\"\")"]

{"sheet":"Portable version","index":3,"n_rows":82,"n_cols":14,"rows_kept":82,"cut":false,"hidden":false,"merged":[[40,0,40,11],[36,0,36,11],[37,0,37,11],[49,0,49,11],[72,0,73,2],[53,0,53,11],[55,0,55,11],[54,0,54,11],[57,0,57,11],[67,0,67,2],[79,0,80,2],[81,0,82,2],[19,0,19,11],[12,0,12,11],[13,0,13,11],[14,0,14,11],[15,0,15,11],[17,0,17,11],[33,0,33,11],[42,0,42,11],[23,0,23,11],[24,0,24,11],[26,0,26,11],[28,0,28,11],[30,0,30,11],[46,0,46,11]]}
["Text to Columns Template - Portable Version"]
["(The portable version is in cell A54)",null,null,null,null,null,null,null,null,null,null,"Created by Catalin Bombea"]
["Delimiter:","/"]
[]
["Paste Data starting in Cell A6:","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN(A6:$B6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN(A6:$B6))+(COLUMN()-1))-SUMPRODUCT(LEN(A6:$B6))-(COLUMN()-1))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN(B6:$B6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN(B6:$B6))+(COLUMN()-1))-SUMPRODUCT(LEN(B6:$B6))-(COLUMN()-1))),\"\")","v":"2"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:C6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:C6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:C6))-(COLUMN()-1))),\"\")","v":"3"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:D6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:D6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:D6))-(COLUMN()-1))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:E6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:E6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:E6))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:F6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:F6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:F6))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:G6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:G6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:G6))-(COLUMN()-1))),\"\")","v":"7"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:H6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:H6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:H6))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:I6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:I6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:I6))-(COLUMN()-1))),\"\")","v":"9"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:J6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:J6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:J6))-(COLUMN()-1))),\"\")","v":"10"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN($B6:K6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN($B6:K6))+(COLUMN()-1))-SUMPRODUCT(LEN($B6:K6))-(COLUMN()-1))),\"\")","v":""}]
["A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN(A7:$B7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN(A7:$B7))+(COLUMN()-1))-SUMPRODUCT(LEN(A7:$B7))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN(B7:$B7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN(B7:$B7))+(COLUMN()-1))-SUMPRODUCT(LEN(B7:$B7))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:C7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:C7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:C7))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:D7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:D7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:D7))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:E7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:E7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:E7))-(COLUMN()-1))),\"\")","v":"E"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:F7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:F7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:F7))-(COLUMN()-1))),\"\")","v":"F"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:G7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:G7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:G7))-(COLUMN()-1))),\"\")","v":"G"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:H7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:H7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:H7))-(COLUMN()-1))),\"\")","v":"H"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:I7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:I7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:I7))-(COLUMN()-1))),\"\")","v":"I"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:J7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:J7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:J7))-(COLUMN()-1))),\"\")","v":"J"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A7,FIND($B$3,$A7)-1),MID($A7,SUMPRODUCT(LEN($B7:K7))+(COLUMN()-1),FIND($B$3,$A7&$B$3,SUMPRODUCT(LEN($B7:K7))+(COLUMN()-1))-SUMPRODUCT(LEN($B7:K7))-(COLUMN()-1))),\"\")","v":""}]
["D/C/B/A/1",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN(A8:$B8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN(A8:$B8))+(COLUMN()-1))-SUMPRODUCT(LEN(A8:$B8))-(COLUMN()-1))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN(B8:$B8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN(B8:$B8))+(COLUMN()-1))-SUMPRODUCT(LEN(B8:$B8))-(COLUMN()-1))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:C8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:C8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:C8))-(COLUMN()-1))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:D8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:D8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:D8))-(COLUMN()-1))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:E8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:E8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:E8))-(COLUMN()-1))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:F8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:F8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:F8))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:G8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:G8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:G8))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:H8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:H8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:H8))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:I8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:I8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:I8))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:J8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:J8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:J8))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A8,FIND($B$3,$A8)-1),MID($A8,SUMPRODUCT(LEN($B8:K8))+(COLUMN()-1),FIND($B$3,$A8&$B$3,SUMPRODUCT(LEN($B8:K8))+(COLUMN()-1))-SUMPRODUCT(LEN($B8:K8))-(COLUMN()-1))),\"\")","v":""}]
["5/6/6/8/4",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN(A9:$B9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN(A9:$B9))+(COLUMN()-1))-SUMPRODUCT(LEN(A9:$B9))-(COLUMN()-1))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN(B9:$B9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN(B9:$B9))+(COLUMN()-1))-SUMPRODUCT(LEN(B9:$B9))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:C9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:C9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:C9))-(COLUMN()-1))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:D9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:D9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:D9))-(COLUMN()-1))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:E9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:E9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:E9))-(COLUMN()-1))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:F9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:F9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:F9))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:G9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:G9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:G9))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:H9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:H9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:H9))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:I9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:I9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:I9))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:J9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:J9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:J9))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A9,FIND($B$3,$A9)-1),MID($A9,SUMPRODUCT(LEN($B9:K9))+(COLUMN()-1),FIND($B$3,$A9&$B$3,SUMPRODUCT(LEN($B9:K9))+(COLUMN()-1))-SUMPRODUCT(LEN($B9:K9))-(COLUMN()-1))),\"\")","v":""}]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN(A10:$B10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN(A10:$B10))+(COLUMN()-1))-SUMPRODUCT(LEN(A10:$B10))-(COLUMN()-1))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN(B10:$B10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN(B10:$B10))+(COLUMN()-1))-SUMPRODUCT(LEN(B10:$B10))-(COLUMN()-1))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:C10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:C10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:C10))-(COLUMN()-1))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:D10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:D10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:D10))-(COLUMN()-1))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:E10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:E10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:E10))-(COLUMN()-1))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:F10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:F10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:F10))-(COLUMN()-1))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:G10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:G10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:G10))-(COLUMN()-1))),\"\")","v":"Saturday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:H10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:H10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:H10))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:I10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:I10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:I10))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:J10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:J10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:J10))-(COLUMN()-1))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A10,FIND($B$3,$A10)-1),MID($A10,SUMPRODUCT(LEN($B10:K10))+(COLUMN()-1),FIND($B$3,$A10&$B$3,SUMPRODUCT(LEN($B10:K10))+(COLUMN()-1))-SUMPRODUCT(LEN($B10:K10))-(COLUMN()-1))),\"\")","v":""}]
[]
[]
["Can we make this formula work anywhere in a worksheet? Let's take look to the formula, to identify the \"weak\" points:"]
["=IFERROR(IF(COLUMN()=2,LEFT($A6,FIND($B$3,$A6)-1),MID($A6,SUMPRODUCT(LEN(A6:$B6))+(COLUMN()-1),FIND($B$3,$A6&$B$3,SUMPRODUCT(LEN(A6:$B6))+(COLUMN()-1))-SUMPRODUCT(LEN(A6:$B6))-(COLUMN()-1))),\"\")"]
["We have 3 parts that will not work in a portable version, all of them are \"tied\" to column B, where we have the first value extracted: COLUMN()=2, (COLUMN()-1), SUMPRODUCT(LEN(A6:$B6)); we have to analyze them and replace with dynamic versions."]
["The first part: COLUMN()=2 returns TRUE only for column B:"]
[null,{"$f":"COLUMN()=2","v":true},{"$f":"COLUMN()=2","v":false},false,false,false,false,false,false]
[" The key here is to understand the meaning of column B: represents the first column next to the source text string, so we need a formula that will return TRUE for this column only:"]
["Text string",{"$f":"COLUMN()=COLUMN($A19)+1","v":true},false,false,false,false,false,false,false]
["This formula should work wherever the source text string is located:"]
[]
[null,"Text string",{"$f":"COLUMN()=COLUMN($B22)+1","v":true},{"$f":"COLUMN()=COLUMN($B22)+1","v":false},false,false,false,false,false,false]
[]
["Works great, let's take the next part: COLUMN()-1. It does not look like it's tied to column B, but it is, because the full formula will start working in column C, and COLUMN()-1 must return 2 for the second value to extract, 3 for the 3'rd value to extract, and so on . (in fact, this number represents the number of delimiters). "]
["Let's look at what COLUMN() -1 returns, and try to create a dynamic formula to imitate that result: (remember that our formula will start to work for the second value from the text string):"]
["Text string",null,{"$f":"COLUMN()-1","v":2},3,4,5,6,7,8]
["If we move the source text string, our second value is 3 instead of 2, but we want the results to look exactly as above, no matter where the source text string is located…"]
[null,"Text string",null,3,4,5,6,7,8,9]
["Like the first part, the solution for this one is simple:"]
[null,"Text string",null,{"$f":"COLUMN()-COLUMN($B30)","v":2},{"$f":"COLUMN()-COLUMN($B30)","v":3},4,5,6,7,8]
["And the solution works wherever the string is located:"]
["Text string",null,{"$f":"COLUMN()-COLUMN($A32)","v":2},{"$f":"COLUMN()-COLUMN($A32)","v":3},4,5,6,7,8]
[]
["The 3'rd part ( the hardest one :) ): SUMPRODUCT(LEN(A6:$B6)). It's purpose is to count the number of characters of all previously extracted values:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN(A35:$B35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN(A35:$B35))+(COLUMN()-1))-SUMPRODUCT(LEN(A35:$B35))-(COLUMN()-1))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN(B35:$B35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN(B35:$B35))+(COLUMN()-1))-SUMPRODUCT(LEN(B35:$B35))-(COLUMN()-1))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN($B35:C35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN($B35:C35))+(COLUMN()-1))-SUMPRODUCT(LEN($B35:C35))-(COLUMN()-1))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN($B35:D35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN($B35:D35))+(COLUMN()-1))-SUMPRODUCT(LEN($B35:D35))-(COLUMN()-1))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN($B35:E35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN($B35:E35))+(COLUMN()-1))-SUMPRODUCT(LEN($B35:E35))-(COLUMN()-1))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN($B35:F35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN($B35:F35))+(COLUMN()-1))-SUMPRODUCT(LEN($B35:F35))-(COLUMN()-1))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=2,LEFT($A35,FIND($B$3,$A35)-1),MID($A35,SUMPRODUCT(LEN($B35:G35))+(COLUMN()-1),FIND($B$3,$A35&$B$3,SUMPRODUCT(LEN($B35:G35))+(COLUMN()-1))-SUMPRODUCT(LEN($B35:G35))-(COLUMN()-1))),\"\")","v":"Saturday"}]
["SUMPRODUCT(LEN(A6:$B6)) at work:",{"$f":"SUMPRODUCT(LEN(A35:$B35))","v":62},{"$f":"SUMPRODUCT(LEN(B35:$B35))","v":6},{"$f":"SUMPRODUCT(LEN($B35:C35))","v":12},{"$f":"SUMPRODUCT(LEN($B35:D35))","v":19},{"$f":"SUMPRODUCT(LEN($B35:E35))","v":28},{"$f":"SUMPRODUCT(LEN($B35:F35))","v":36},{"$f":"SUMPRODUCT(LEN($B35:G35))","v":42}]
["Note: remember that the IF condition from the full formula returns another formula to extract the first value, it ignores that wrong result for column B! We need to focus and imitate the next results."]
["To clarify things, we need to read the meaning and functionality of A35:$B35 range. This range is applied for the first extracted value, but when the formula is copied to right, A35 will change to B35, C35 and so on. Let's look at how this range changes when copied to the right:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday","=A35:$B35","=B35:$B35","=$B35:C35","=$B35:D35","=$B35:E35","=$B35:F35","=$B35:G35"]
["Text string","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7"]
["As you already know, the range for Value 1 is ignored. The range we need to count the number of chars is in fact 1 single cell for value 2 (B35) , and refers to the first extracted value. As we move to the right, the range expands to add all previous extracted values to be counted.. For example, the range for Value 7 has 6 cells (B35 to G35), representing all previously extracted values. The challenge is how can we create a dynamic autoexpanding range, that must depend on the cell position of the initial text string? The A35:$B35 range must work like this: instead of A35, we need to have the address of the cell containing the text string, and instead of $B35 (which is locked for column B), we need the address of the cell next to the text string."]
[]
["Create the range using OFFSET function:"]
[null,null,"The formula will work starting from the second value:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday","=SUMPRODUCT(LEN(OFFSET($A45;0;1):OFFSET(A45;0;0)))",{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(B45,0,0)))","v":50},{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(C45,0,0)))","v":52},{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(D45,0,0)))","v":54},{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(E45,0,0)))","v":56},{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(F45,0,0)))","v":58},{"$f":"SUMPRODUCT(LEN(OFFSET($A45,0,1):OFFSET(G45,0,0)))","v":60}]
[null,"The formula returns a circular reference in cell B45, just ignore it, for the first value, the other formula is triggered, not this"]
["If we replace in formula those 3 parts with their dynamic solutions:"]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(A48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(A48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(A48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(B48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(B48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(B48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(C48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(C48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(C48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(D48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(D48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(D48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(E48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(E48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(E48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(F48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(F48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(F48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($A48)+1,LEFT($A48,FIND($B$3,$A48)-1),MID($A48,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(G48,0,0)))+(COLUMN()-COLUMN($A48)),FIND($B$3,$A48&$B$3,SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(G48,0,0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48,0,1):OFFSET(G48,0,0)))-(COLUMN()-COLUMN($A48)))),\"\")","v":"Saturday"}]
[]
["We did it! :)"]
[]
["Let's take a closer look at the final formula: (I know, it looks ugly :) )"]
[]
["=IFERROR(IF(COLUMN()=COLUMN($A48)+1;LEFT($A48;FIND($B$3;$A48)-1);MID($A48;SUMPRODUCT(LEN(OFFSET($A48;0;1):OFFSET(A48;0;0)))+(COLUMN()-COLUMN($A48));FIND($B$3;$A48&$B$3;SUMPRODUCT(LEN(OFFSET($A48;0;1):OFFSET(A48;0;0)))+(COLUMN()-COLUMN($A48)))-SUMPRODUCT(LEN(OFFSET($A48;0;1):OFFSET(A48;0;0)))-(COLUMN()-COLUMN($A48))));\"\")"]
["The entire formula refers only to the cell with the text string to do it's job! (excepting , of course, the reference to the delimiter from cell $B$3)\nThere are 14 references to cell A48: 11 of them have an absolute column reference ($A48), 3 of them are all relative (those inside OFFSET function)"]
["I can't wait, I really have to test it, see if it works anywhere on the sheet:"]
[]
["Tip: copy the formula from A54 (without the first apostrophe) in cell C62, press Enter, then select again cell C62, press CTRL+H, in Find What-type A48, and in Replace-type B62, press ALT+I (or push the button Find All, select cell C62 from list and press Replace. This will change all 14 references to the new location of the text string:"]
[]
[]
[]
["With the text string in B59, copy the formula from A54 in C62:","Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(B62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(B62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(B62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(C62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(C62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(C62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(D62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(D62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(D62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(E62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(E62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(E62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(F62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(F62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(F62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(G62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(G62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(G62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(H62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(H62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(H62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":"Saturday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($B62)+1,LEFT($B62,FIND($B$3,$B62)-1),MID($B62,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(I62,0,0)))+(COLUMN()-COLUMN($B62)),FIND($B$3,$B62&$B$3,SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(I62,0,0)))+(COLUMN()-COLUMN($B62)))-SUMPRODUCT(LEN(OFFSET($B62,0,1):OFFSET(I62,0,0)))-(COLUMN()-COLUMN($B62)))),\"\")","v":""}]
[]
[]
[]
[]
[]
["With the text string in D68, copy the formula from A54 in E68  (use CTRL+H to replace A48 to D68):",null,null,"Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(D68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(D68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(D68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(E68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(E68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(E68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(F68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(F68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(F68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(G68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(G68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(G68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(H68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(H68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(H68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(I68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(I68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(I68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($D68)+1,LEFT($D68,FIND($B$3,$D68)-1),MID($D68,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(J68,0,0)))+(COLUMN()-COLUMN($D68)),FIND($B$3,$D68&$B$3,SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(J68,0,0)))+(COLUMN()-COLUMN($D68)))-SUMPRODUCT(LEN(OFFSET($D68,0,1):OFFSET(J68,0,0)))-(COLUMN()-COLUMN($D68)))),\"\")","v":"Saturday"}]
[]
[]
[]
[]
["Works every time, so I consider the formula Portable :)",null,null,null,"1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(E73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(E73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(E73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(F73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(F73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(F73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"2"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(G73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(G73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(G73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"3"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(H73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(H73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(H73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(I73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(I73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(I73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(J73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(J73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(J73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(K73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(K73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(K73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"7"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(L73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(L73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(L73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E73)+1,LEFT($E73,FIND($B$3,$E73)-1),MID($E73,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(M73,0,0)))+(COLUMN()-COLUMN($E73)),FIND($B$3,$E73&$B$3,SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(M73,0,0)))+(COLUMN()-COLUMN($E73)))-SUMPRODUCT(LEN(OFFSET($E73,0,1):OFFSET(M73,0,0)))-(COLUMN()-COLUMN($E73)))),\"\")","v":"9"}]
[null,null,null,null,"A/B/C/D/E/F/G/H/I/J",{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(E74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(E74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(E74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(F74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(F74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(F74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(G74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(G74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(G74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(H74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(H74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(H74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(I74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(I74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(I74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"E"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(J74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(J74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(J74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"F"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(K74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(K74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(K74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"G"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(L74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(L74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(L74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"H"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E74)+1,LEFT($E74,FIND($B$3,$E74)-1),MID($E74,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(M74,0,0)))+(COLUMN()-COLUMN($E74)),FIND($B$3,$E74&$B$3,SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(M74,0,0)))+(COLUMN()-COLUMN($E74)))-SUMPRODUCT(LEN(OFFSET($E74,0,1):OFFSET(M74,0,0)))-(COLUMN()-COLUMN($E74)))),\"\")","v":"I"}]
[null,null,null,null,"D/C/B/A/1",{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(E75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(E75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(E75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":"D"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(F75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(F75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(F75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":"C"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(G75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(G75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(G75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":"B"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(H75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(H75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(H75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":"A"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(I75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(I75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(I75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":"1"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(J75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(J75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(J75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(K75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(K75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(K75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(L75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(L75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(L75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E75)+1,LEFT($E75,FIND($B$3,$E75)-1),MID($E75,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(M75,0,0)))+(COLUMN()-COLUMN($E75)),FIND($B$3,$E75&$B$3,SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(M75,0,0)))+(COLUMN()-COLUMN($E75)))-SUMPRODUCT(LEN(OFFSET($E75,0,1):OFFSET(M75,0,0)))-(COLUMN()-COLUMN($E75)))),\"\")","v":""}]
[null,null,null,null,"5/6/6/8/4",{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(E76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(E76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(E76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":"5"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(F76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(F76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(F76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(G76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(G76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(G76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":"6"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(H76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(H76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(H76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":"8"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(I76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(I76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(I76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":"4"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(J76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(J76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(J76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(K76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(K76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(K76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(L76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(L76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(L76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E76)+1,LEFT($E76,FIND($B$3,$E76)-1),MID($E76,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(M76,0,0)))+(COLUMN()-COLUMN($E76)),FIND($B$3,$E76&$B$3,SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(M76,0,0)))+(COLUMN()-COLUMN($E76)))-SUMPRODUCT(LEN(OFFSET($E76,0,1):OFFSET(M76,0,0)))-(COLUMN()-COLUMN($E76)))),\"\")","v":""}]
[null,null,null,null,"Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday",{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(E77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(E77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(E77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Sunday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(F77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(F77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(F77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Monday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(G77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(G77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(G77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Tuesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(H77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(H77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(H77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Wednesday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(I77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(I77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(I77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Thursday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(J77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(J77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(J77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Friday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(K77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(K77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(K77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":"Saturday"},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(L77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(L77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(L77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":""},{"$f":"IFERROR(IF(COLUMN()=COLUMN($E77)+1,LEFT($E77,FIND($B$3,$E77)-1),MID($E77,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(M77,0,0)))+(COLUMN()-COLUMN($E77)),FIND($B$3,$E77&$B$3,SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(M77,0,0)))+(COLUMN()-COLUMN($E77)))-SUMPRODUCT(LEN(OFFSET($E77,0,1):OFFSET(M77,0,0)))-(COLUMN()-COLUMN($E77)))),\"\")","v":""}]
[]
[]
["Can we add to it the \"Treat consecutive delimiters as one\" option?"]
[]
["What a sick question…"]

{"sheet":"Text toCol using VBA","index":4,"n_rows":16,"n_cols":12,"rows_kept":16,"cut":false,"hidden":false,"merged":[[11,0,11,11],[12,1,12,4]]}
["Text to Columns Template - Consecutive Delimiters"]
[null,null,null,null,null,null,null,null,null,null,null,"Created by Catalin Bombea"]
["Delimiter:","/"]
[]
["Data","Value 1","Value 2","Value 3","Value 4","Value 5","Value 6","Value 7","Value 8","Value 9","Value 10","Value 11"]
["1/2/3/4/5/6/7/8/9/10",{"$f":"IFERROR(SplitText($A6,COLUMN()-1,$B$3),\"\")","v":"1"},{"$f":"IFERROR(SplitText($A6,COLUMN()-1,$B$3),\"\")","v":"2"},"3","4","5","6","7","8","9","10",""]
["A/B/C/D/E/F/G/H/I/J","A","B","C","D","E","F","G","H","I","J",""]
["D/C/B/A/1","D","C","B","A","1","","","","","",""]
["5/6/6/8/4","5","6","6","8","4","","","","","",""]
["Sunday/Monday/Tuesday/Wednesday/Thursday/Friday/Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","","","",""]
[]
["Tip: after typing the function name: =SplitText(|  with the cursor placed after the first open paranthesis, press CTRL+SHIFT+A, this command will bring up the arguments of this function! (When using User Defined Functions, the formula editor is not working like built-in function, only the newer versions of excel we have some improvements...)\nYou will get this: =SplitText(Text,Position,Delimiter) . Not colored, of course :) Then just replace argument text with reference and desired parameters.\nNote that the third parameter is optional, the default delimiter is set in code to be a space character: Optional Delimiter As Variant = \" \" . You can press Alt+F11 to change the default delimiter to your needs. Or, to override the optional default delimiter, just fill the third argument of the function with your desired character, like: =SplitText($A16,1,\"/\")"]
["Sunday Monday Tuesday /Wednesday Thursday Friday Saturday",{"$f":"SplitText($A13,1,\"/\")","v":"Sunday Monday Tuesday "}]
[]
["If you omit to fill the third argument, the delimiter, the text string will be delimited using a space as the default delimiter:"]
["Sunday Monday Tuesday /Wednesday Thursday Friday Saturday",{"$f":"SplitText($A16,1)","v":"Sunday"}]