LinkedIn post by:
Caroline Blake
=MAP(A2:A8,LAMBDA(x,LET(a,CODE(x),b,(CODE(RIGHT(x,1))),
c,(CHAR(SEQUENCE(,b-a+1,a))),d,IF(ISERROR(FIND(c,x)),c,""),
IFERROR(ARRAYTOTEXT(FILTER(d,ISNUMBER(CODE(d))=TRUE)),""))))
=MAP(A2:A8,LAMBDA(x,LET(a,CODE(x),b,(CODE(RIGHT(x,1))),
c,(CHAR(SEQUENCE(,b-a+1,a))),d,IF(ISERROR(FIND(c,x)),c,""),
IFERROR(ARRAYTOTEXT(FILTER(d,ISNUMBER(CODE(d))=TRUE)),""))))
M-Code
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformRows(Source,each let c = Text.ToList([String]) in Text.Combine(List.Difference({List.Min(c)..List.Max(c)},c) ,", "))
in
Ans
=MAP(A2:A8,LAMBDA(a,LET(e,CHAR(SEQUENCE(26,,97)),r,FILTER(e,(e>=LEFT(a))*(e<=RIGHT(a))),TEXTJOIN(", ",, IF(ISERROR(SEARCH(r,a)),r,"")))))
=MAP(A2:A8,LAMBDA(x,LET(l,CODE(x),r,CODE(RIGHT(x)),a,CHAR(SEQUENCE(r-l+1,,l)),TEXTJOIN(", ",,FILTER(a,ISERR(FIND(a,x)),"")))))
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Custom1 = List.Transform(Source[String], each Text.Combine(List.Difference({Text.At(_,0)..Text.At(_,Text.Length(_)-1)},Text.ToList(_)),", "))
in
Custom1
=MAP(A2:A8,LAMBDA(a,LET(e,CHAR(SEQUENCE(26)+96),r,FILTER(e,(e>=LEFT(a))*(e<=RIGHT(a))),TEXTJOIN(", ",, IF(ISERR(FIND(r,a)),r,"")))))
=BYROW(A2:A8,LAMBDA(string,LET(left,CODE(LEFT(string,1)),right,CODE(RIGHT(string,1)),complete,CHAR(SEQUENCE(1+right-left,,left)),TEXTJOIN(", ",1,FILTER(complete,ISERROR(FIND(complete,string)),"")))))
let
Fonte = Tabela1,
res = Table.AddColumn(Fonte, "Personalizar", each [
a = Text.ToList([String]),
b = Text.Combine(List.Difference({List.Min(a)..List.Max(a)},a),", ")
][b])
in
res
=MAP(A2:A8,LAMBDA(x,LET(a,CODE(LEFT(x)),b,CHAR(SEQUENCE(CODE(RIGHT(x))-a+1,,a)),ARRAYTOTEXT(FILTER(b,ISERR(FIND(b,x)),"")))))
Thank you Excel BI
=IFERROR(MAP(A2:A8, LAMBDA(x, LET(a, CHAR(SEQUENCE(26,,97)), l, XMATCH(LEFT(x),a), r, XMATCH(RIGHT(x),a), m, TAKE(DROP(a,l-1),r-l+1), TEXTJOIN(", ",,FILTER(m,ISERR(FIND(m,x))))))), "")
Envio mi soluccion.
=LET(a;CODIGO(EXTRAE(A2;SECUENCIA(LARGO(A2));1));b;MAX(a)-MIN(a);c;SECUENCIA(b;;MIN(a);1);d;CARACTER(SI(ESERROR(BUSCARX(c;a;a));c;""));UNIRCADENAS(",";VERDADERO;SI.ERROR(d;"")))
Power Query
let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(Origen[String], each Text.ToList(_)),
b = List.Transform(a, each {List.Min(_)..List.Max(_)}),
c = List.Transform({0..List.Count(a)-1}, each List.RemoveMatchingItems(b{_}, a{_})),
d = List.Transform(c, each Text.Combine(_, ", "))
in
d
=MAP(A2:A8,LAMBDA(A,TEXTJOIN(", ",1,LET(u,CODE(MID(A,SEQUENCE(LEN(A)),1)),n,SEQUENCE(@TAKE(u,-1)-@u+1,,@u),CHAR(FILTER(n,NOT(MMULT(--(n=TRANSPOSE(u)),u/u)),10))))))