Trim Characters Before Asterisk

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. Na*tio**n => Ntn Bi***ll => ll

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 194
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Trim Characters Before Asterisk with Power Query

Power Query solution 1 for Trim Characters Before Asterisk, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.TransformColumns(
    Source, 
    {}, 
    each List.Accumulate(
      {1 .. Text.Length(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 solution 2 for Trim Characters Before Asterisk, proposed 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

Power Query solution 3 for Trim Characters Before Asterisk, proposed by Zoran Milokanović:

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

Power Query solution 4 for Trim Characters Before Asterisk, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.AddColumn(
    Source, 
    "Answer", 
    each 
      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
        Text.Combine(b, "")
  )[[Answer]]
in
  Sol

Solving the challenge of Trim Characters Before Asterisk with Excel

Excel solution 1 for Trim Characters Before Asterisk, proposed 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))))

Excel solution 2 for Trim Characters Before Asterisk, proposed 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),))))

Excel solution 3 for Trim Characters Before Asterisk, proposed 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)),"")))))

Excel solution 4 for Trim Characters Before Asterisk, proposed by محمد حلمي:

Excel solution 5 for Trim Characters Before Asterisk, proposed by Julian Poeltl:

=MAP(A2:A10,LAMBDA(S,LET(T,TEXTSPLIT(S,"*"),TT,FILTER(T,T<>""),L,LEN(TT),A,TEXTSPLIT(S,CHAR(60+SEQUENCE(99))),F,EXPAND(LEN(FILTER(A,A<>"")),,COLUMNS(TT),0),CONCAT(LEFT(TT,IF(F>L,0,L-F))))))

Excel solution 6 for Trim Characters Before Asterisk, proposed by Timothée BLIOT:

=MAP(A2:A10,LAMBDA(z,LET(A,TEXTSPLIT(z,"*",,1),B,DROP(TEXTSPLIT(z,A),,1),CONCAT(MAP(A,B,LAMBDA(x,y,IFERROR(LEFT(x,LEN(x)-LEN(y)),"")))))))

Excel solution 7 for Trim Characters Before Asterisk, proposed by Hussein SATOUR:

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

Excel solution 8 for Trim Characters Before Asterisk, proposed by Oscar Mendez Roca Farell:

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

Excel solution 9 for Trim Characters Before Asterisk, proposed by Md. Zohurul Islam:

=MAP(A2:A10,LAMBDA(x,LET(cr,CHAR(SEQUENCE(26,,65)),
a,TEXTSPLIT(x,"*",,1),
b,HSTACK(a,""),
c,TEXTSPLIT(UPPER(x),cr,,1),
d,LEN(c),
e,LEN(b)-HSTACK(d,0),
f,IFERROR(LEFT(b,e),""),
g,CONCAT(f),
g)
))

Excel solution 10 for Trim Characters Before Asterisk, proposed by Charles Roldan:

=LET(
 Data, A2:A10,
 Blanks, REPT("", Data = Data),
 Through, LAMBDA(F, F(F))(
 LAMBDA(f,
 LAMBDA(Ahead, Behind,
 IF(
 Ahead = "",
 Behind,
 LET(
 Omit, LEFT(Ahead) = "*",
 f(f)(
 RIGHT(Ahead, LEN(Ahead) - 1),
 LEFT(Behind, MAX(, LEN(Behind) - Omit)) &
 REPT(LEFT(Ahead), NOT(Omit))
 )
 )
 )
 )
 )
 ),
 MAP(Data, Blanks, Through)
)

Excel solution 11 for Trim Characters Before Asterisk, proposed by Charles Roldan:

=MAP(A2:A10,
 LAMBDA(String, 
 REDUCE(, TEXTSPLIT(String, "*"), 
 LAMBDA(Old, New, LEFT(Old, MAX(LEN(Old) - 1, )) & New)
)   )   )

Excel solution 12 for Trim Characters Before Asterisk, proposed 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)))))))

Excel solution 13 for Trim Characters Before Asterisk, proposed by Stevenson Yu:

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

Excel solution 14 for Trim Characters Before Asterisk, proposed 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)

&&&

Solving the challenge of Trim Characters Before Asterisk with Power Query

Solving the challenge of Trim Characters Before Asterisk with Excel

Leave a Reply Cancel reply