{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Sheet1"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"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":"Sheet1","index":0,"n_rows":38,"n_cols":8,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
[null,"Extracting one element of a specific number:"]
[null,null,{"$f":"IFERROR(0+MID(\"α\"&String&\"α0\",1+SMALL(IF(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1})=2,ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),elem),SUM(SMALL(IF(ISNUMBER(MATCH(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1}),{1,2},0)),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),2*elem+{-1,0})*{-1,1})),\"\")","v":""},null,null,null,null,{"$f":"IFERROR(0+MID(\"α\"&String&\"α0\",1+SMALL(IF(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1})=2,ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),elem),SUM(SMALL(IF(ISNUMBER(MATCH(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1}),{1,2},0)),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),2*elem+{-1,0})*{-1,1})),\"\")","v":""}]
[]
[null,"String","Element","Result:"]
[null,"oe4py646aeou1bx54wmb64",3,{"$f":"MID(String,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&String,ROW(OFFSET($B$1,,,LEN(String))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN(String)))),element)-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&String&\"|\",ROW(OFFSET($B$1,,,LEN(String)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN(String)+1))),{1,2}+(element-1)*2)*{-1,1}))","v":"1"},"  <<< Jeff's approach",null,{"$f":"LEN(H5)","v":323},{"$f":"showformula(D5)","v":"=MID(String,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&String,ROW(OFFSET($B$1,,,LEN(String))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN(String)))),element)-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&String&\"|\",ROW(OFFSET($B$1,,,LEN(String)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN(String)+1))),{1,2}+(element-1)*2)*{-1,1}))"}]
[null,null,null,{"$f":"0+MID(\"α\"&String&\"α0\",1+SMALL(IF(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1})=2,ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),element),SUM(SMALL(IF(ISNUMBER(MATCH(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1}),{1,2},0)),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),2*element+{-1,0})*{-1,1}))","v":1},"  <<< ExcelXOR's approach",null,{"$f":"LEN(H6)","v":463},{"$f":"showformula(D6)","v":"=0+MID(\"α\"&String&\"α0\",1+SMALL(IF(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1})=2,ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),element),SUM(SMALL(IF(ISNUMBER(MATCH(MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(\"α\"&String&\"α0\",\"/\",\"α\"),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))+{0,1},1)))>6)*{2,1},{1;1}),{1,2},0)),ROW(INDIRECT(\"1:\"&LEN(\"α\"&String&\"α0\")-1))),2*element+{-1,0})*{-1,1}))"}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Extracting all numbers into separate columns:"]
[]
[null,"Strings","1st number","2nd number","3rd number","4th number"]
[null,"1a22bb333ccc4444dddd",{"$f":"MID($B35,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B35,ROW(OFFSET($B$1,,,LEN($B35))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)))),COLUMNS($C35:C35))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B35&\"|\",ROW(OFFSET($B$1,,,LEN($B35)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)+1))),{1,2}+(COLUMNS($C35:C35)-1)*2)*{-1,1}))","v":"1"},{"$f":"MID($B35,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B35,ROW(OFFSET($B$1,,,LEN($B35))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)))),COLUMNS($C35:D35))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B35&\"|\",ROW(OFFSET($B$1,,,LEN($B35)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)+1))),{1,2}+(COLUMNS($C35:D35)-1)*2)*{-1,1}))","v":"22"},{"$f":"MID($B35,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B35,ROW(OFFSET($B$1,,,LEN($B35))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)))),COLUMNS($C35:E35))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B35&\"|\",ROW(OFFSET($B$1,,,LEN($B35)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)+1))),{1,2}+(COLUMNS($C35:E35)-1)*2)*{-1,1}))","v":"333"},{"$f":"MID($B35,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B35,ROW(OFFSET($B$1,,,LEN($B35))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)))),COLUMNS($C35:F35))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B35&\"|\",ROW(OFFSET($B$1,,,LEN($B35)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B35)+1))),{1,2}+(COLUMNS($C35:F35)-1)*2)*{-1,1}))","v":"4444"}]
[null,"a1bb22ccc3333dddd4444",{"$f":"MID($B36,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B36,ROW(OFFSET($B$1,,,LEN($B36))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)))),COLUMNS($C36:C36))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B36&\"|\",ROW(OFFSET($B$1,,,LEN($B36)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)+1))),{1,2}+(COLUMNS($C36:C36)-1)*2)*{-1,1}))","v":"1"},{"$f":"MID($B36,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B36,ROW(OFFSET($B$1,,,LEN($B36))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)))),COLUMNS($C36:D36))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B36&\"|\",ROW(OFFSET($B$1,,,LEN($B36)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)+1))),{1,2}+(COLUMNS($C36:D36)-1)*2)*{-1,1}))","v":"22"},{"$f":"MID($B36,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B36,ROW(OFFSET($B$1,,,LEN($B36))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)))),COLUMNS($C36:E36))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B36&\"|\",ROW(OFFSET($B$1,,,LEN($B36)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)+1))),{1,2}+(COLUMNS($C36:E36)-1)*2)*{-1,1}))","v":"3333"},{"$f":"MID($B36,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B36,ROW(OFFSET($B$1,,,LEN($B36))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)))),COLUMNS($C36:F36))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B36&\"|\",ROW(OFFSET($B$1,,,LEN($B36)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B36)+1))),{1,2}+(COLUMNS($C36:F36)-1)*2)*{-1,1}))","v":"4444"}]
[null,"dddd4444ccc333bb22a1",{"$f":"MID($B37,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B37,ROW(OFFSET($B$1,,,LEN($B37))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)))),COLUMNS($C37:C37))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B37&\"|\",ROW(OFFSET($B$1,,,LEN($B37)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)+1))),{1,2}+(COLUMNS($C37:C37)-1)*2)*{-1,1}))","v":"4444"},{"$f":"MID($B37,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B37,ROW(OFFSET($B$1,,,LEN($B37))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)))),COLUMNS($C37:D37))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B37&\"|\",ROW(OFFSET($B$1,,,LEN($B37)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)+1))),{1,2}+(COLUMNS($C37:D37)-1)*2)*{-1,1}))","v":"333"},{"$f":"MID($B37,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B37,ROW(OFFSET($B$1,,,LEN($B37))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)))),COLUMNS($C37:E37))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B37&\"|\",ROW(OFFSET($B$1,,,LEN($B37)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)+1))),{1,2}+(COLUMNS($C37:E37)-1)*2)*{-1,1}))","v":"22"},{"$f":"MID($B37,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B37,ROW(OFFSET($B$1,,,LEN($B37))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)))),COLUMNS($C37:F37))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B37&\"|\",ROW(OFFSET($B$1,,,LEN($B37)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B37)+1))),{1,2}+(COLUMNS($C37:F37)-1)*2)*{-1,1}))","v":{"$error":"#NUM!"}}]
[null,"4444dddd333ccc22bb1a",{"$f":"MID($B38,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B38,ROW(OFFSET($B$1,,,LEN($B38))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)))),COLUMNS($C38:C38))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B38&\"|\",ROW(OFFSET($B$1,,,LEN($B38)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)+1))),{1,2}+(COLUMNS($C38:C38)-1)*2)*{-1,1}))","v":"4444"},{"$f":"MID($B38,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B38,ROW(OFFSET($B$1,,,LEN($B38))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)))),COLUMNS($C38:D38))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B38&\"|\",ROW(OFFSET($B$1,,,LEN($B38)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)+1))),{1,2}+(COLUMNS($C38:D38)-1)*2)*{-1,1}))","v":"333"},{"$f":"MID($B38,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B38,ROW(OFFSET($B$1,,,LEN($B38))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)))),COLUMNS($C38:E38))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B38&\"|\",ROW(OFFSET($B$1,,,LEN($B38)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)+1))),{1,2}+(COLUMNS($C38:E38)-1)*2)*{-1,1}))","v":"22"},{"$f":"MID($B38,SMALL(IF(ISERROR(-MID(TEXT(MID(\"||\"&$B38,ROW(OFFSET($B$1,,,LEN($B38))),2),\"|\"),2,1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)))),COLUMNS($C38:F38))-1,SUM(SMALL(IF(ISERROR(-MID(TEXT(MID(\"|\"&$B38&\"|\",ROW(OFFSET($B$1,,,LEN($B38)+1)),2),\"|\"),{1,2},1)),FALSE,ROW(OFFSET($B$1,,,LEN($B38)+1))),{1,2}+(COLUMNS($C38:F38)-1)*2)*{-1,1}))","v":"1"}]