let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.AddColumn(Source, "Split", each Text.Combine(List.Select(
Text.SplitAny([TEXT], Text.Remove([TEXT], {"0".."9"})),
each _<> ""), ", "))
in
Split
PascalNo good for @petter_hoang , I know, but for more recent versions of Excel:
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1))
View attachment 81449
And for numeric results add VALUE():
=LET(b,A4,a,MID(b, SEQUENCE(,LEN(b)), 1),VALUE(TEXTSPLIT(b,FILTER(a,ISERR(a*1)),,TRUE,1)))
ExtractNumλ
= LAMBDA(txt,
LET(
chr, MID(txt, SEQUENCE(,LEN(txt)), 1),
sep, FILTER(chr,ISERR(chr*1)),
VALUE(TEXTSPLIT(txt,sep,,TRUE,1))
)
);
= ExtractNumλ(@text)
= MAP(text, ExtractNumλ)
= MapVλ(text, ExtractNumλ)
where
MapVλ
= LAMBDA(arr, fnλ,
DROP(
REDUCE("",arr,
LAMBDA(stk, a, VSTACK(stk, fnλ(a)))
),
1 )
)