# 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

Post answers in Comment.

(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

Excel BI’s LinkedIn Post

## LinkedIn post by:JvdV

‘=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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Answer", each List.Accumulate(Text.Split([String],"*"),"",(a,b)=> Text.Trim(Text.Start(" " & a, Text.Length(a)) & b)))
in
``````

## LinkedIn post by:Stevenson Yu

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)))
``````

## LinkedIn post by:Alejandro Simón

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
``````

## LinkedIn post by:Lorenzo Foti

``````=+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)
``````

## LinkedIn Post by:Bo Rydobon

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
``````

## LinkedIn Post by:Guillermo Arroyo

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
``````

## LinkedIn post by:Zoran Milokanović

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))))), "")))))`

## LinkedIn post by:محمد حلمي

``````=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)),"")))))
``````

## LinkedIn post by:Bo Rydobon

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

## LinkedIn Post by:Bo Rydobon

``````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!

## LinkedIn post by:محمد حلمي

``````=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)),"")))))
``````

## LinkedIn post by:Bo Rydobon

`=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),))))`

## LinkedIn post by:Hussein SATOUR

Thank you Excel BI

``````=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)))))
``````

## LinkedIn Post by:Guillermo Arroyo

``````=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)))))))
``````