# Excel BI Challenge Workout 194

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 194 —
(Solutions in any language are also welcome for Excel Challenges)

Remove the characters preceding the asterisks. The number of characters to be removed will be equal to number of asterisks. If number of asterisks are more than number of characters on the left, then remove all characters on the left.
Nation => Ntn
Bi
ll => ll

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

‘=REDUCE(0,TEXTSPLIT(A2,“*”),LAMBDA(a,b,TRIM(LEFT(" "&a,LEN(a)))&b))’

Or, for the whole range at once:

`=MAP(A2:A10,LAMBDA(c,REDUCE(0,TEXTSPLIT(c,"*"),LAMBDA(a,b,TRIM(LEFT(" "&a,LEN(a)))&b))))`

Or, apply the same logic to PQ with M-code:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"String", type text}}),
in
``````

This is so very ugly, but I finally got it to work. I had the most difficulty in counting the number of asterisks.

``````=MAP(A2:A10, LAMBDA(Q,
LET(A, Q,
B, TEXTSPLIT(A,"*"),
C, FILTER(B, B<>""),
D, LEN(C),
E, LOWER(REDUCE(A, REPT("*",SEQUENCE(LEN(A),,LEN(A),-1)), LAMBDA(X,Y, SUBSTITUTE(X, Y, LEN(Y))))),
F, REDUCE(E, CHAR(SEQUENCE(26,,97)), LAMBDA(X,Y, SUBSTITUTE(X, Y, "|"))),
G, TEXTSPLIT(F,"|"),
H, --EXPAND(FILTER(G, G<>""),1,COUNTA(C),0),
I, IF(H>D,0,D-H),
J, LEFT(C,I),
K, TEXTJOIN("",1,J), K)))
``````

tarde, pero aqui les adjunto mi query…

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
let
a = Text.ToList([String]),
b = List.Accumulate(a, {}, (s,c)=> if c <> "*" then s&{c} else try List.FirstN(s, List.Count(s)-1) otherwise {} )
in
Sol
``````

``````=+LET(
str;MID(A2;SEQUENCE(LEN(A2));1);
outP;REDUCE("";str;LAMBDA(a;v;IF(v="*";IFERROR(LEFT(a;LEN(a)-1);"");a&v)));
outP)
``````

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformColumns(Source,{},each List.Accumulate({1..Text.Length([Text.Select](http://text.select/)(_,"*"))},_,(a,w)=> let x= Text.PositionOf(a,"*") in
Text.RemoveRange(a, List.Max({0,x-1}),List.Min({x+1,2} ))))
in
Ans
``````

Power Query

``````let
Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
a = List.Transform(Origen[String], each
let
o = Text.ToList(_),
r = (x,y) =>
if x = List.Count(o)
then y
else @r(x+1, if o{x} = "*" then List.RemoveLastN(y) else List.Combine({y, {o{x}}}))
in
r(0, {""})),
b = List.Transform(a, each Text.Combine(_))
in
b
``````

Removing characters w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each Text.Combine(List.Accumulate(Text.ToList([String]), {}, (s, c) => if c = "*" then List.RemoveLastN(s) else s & {c}), ""))
in
Solution
``````

## LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

`=MAP(A2:A10, LAMBDA(a, LET(_t, TEXTSPLIT(a, ,"*",1), CONCAT( SUBSTITUTE(_t, RIGHT(_t, TOCOL( LEN( TRIM( TEXTSPLIT(LOWER(a)&" ", CHAR( SEQUENCE(26, ,97)), ,1))))), "")))))`

``````=MAP(A2:A10,LAMBDA(a,LET(r,TEXTSPLIT(a,"*",,1),l,
HSTACK(r,""),CONCAT(IFERROR(LEFT(l,LEN(l)-HSTACK(
LEN(TEXTSPLIT(UPPER(a),CHAR(ROW(65:90)),,1)),0)),"")))))
``````

`=REDUCE("",SEQUENCE(50),LAMBDA(a,n,LET(b,MID(A2:A10,n,1),IF(b="*",LEFT(a,LEN(a)-(a>"")),a&b))))`

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.TransformColumns(Source,{},each List.Accumulate(Text.ToList(_),"", (a,v)=> if v="*" then Text.Start(a, Text.Length(a)-Number.From(a>"")) else a&v))
in
Ans
``````

## LinkedIn Post by:John Jairo V.

Hi to all!

One option could be: `=MAP(A2:A10,LAMBDA(x,REDUCE("",MID(x,ROW(1:30),1),LAMBDA(a,v,IF(v="*",LEFT(a,MAX(,LEN(a)-1)),a&v)))))`

Blessings!

``````=MAP(A2:A10,LAMBDA(a,LET(l,HSTACK(TEXTSPLIT(a,"*",,1),""),CONCAT(IFERROR(LEFT(l,LEN(l)-HSTACK(LEN(
TEXTSPLIT(a,CHAR(ROW(65:122)),,1)),0)),"")))))
``````

`=REDUCE(A2:A10,SEQUENCE(20),LAMBDA(a,w,LET(x,IFERROR(FIND("*",a)-1,99),REPLACE(a,IF(x,x,1),IF(x>1,2,1),))))`

``````=MAP(A2:A10, LAMBDA(z, REDUCE(,MID(z,SEQUENCE(LEN(z)),1), LAMBDA(x,y, IF(y="*", IFERROR(LEFT(x, LEN(x)-1), x), x&y)))))
``````=MAP(A2:A10;LAMBDA(a;CONCAT(REDUCE("";MID(a;SEQUENCE(LEN(a));1);LAMBDA(i;j;IF(j="*";IFERROR(DROP(i;-1);"");VSTACK(i;j)))))))