Calculate the remaining visits Strictly Legacy array function or PowerQuery
📌 Challenge Details and Links
Challenge Number: 13
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Clean and Sum a Text String with Power Query
Power Query solution 1 for Clean and Sum a Text String, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table14"]}[Content],
Ans = Table.AddColumn(
Source,
"Remaining",
each Number.Abs(
Expression.Evaluate(
Text.Replace(
Text.Replace(Text.Select([Visits Tracker], {"0" .. "9", "/", "-"}), "-", "+"),
"/",
"-"
)
)
)
)
in
AnsPower Query solution 2 for Clean and Sum a Text String, proposed by Luan Rodrigues:
let
Fonte = Table1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each List.Sum(
List.Transform(
List.Select(
Text.Split(Text.Select([Visits Tracker], {"0" .. "9", "/", " "}), " "),
each _ <> ""
),
each Number.Abs(Expression.Evaluate(Text.Replace(_, "/", "-")))
)
)
)
in
resPower Query solution 3 for Clean and Sum a Text String, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
RemVisits = Table.AddColumn(
Source,
"Remaining Visits",
each Expression.Evaluate(
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(Text.Select([Visits Tracker], {"0" .. "9", "/", "-"})),
{{"/", "+"}, {"-", "-0"}}
)
)
)
)
in
RemVisitsSolving the challenge of Clean and Sum a Text String with Excel
Excel solution 1 for Clean and Sum a Text String, proposed by Rick Rothstein:
=SUM(
TRIM(
MID(
SUBSTITUTE(
" "&TRIM(
CONCAT(
IFERROR(
0+MID(
B3,
ROW(
$1:$200),
1),
" ")))&" 0 0",
" ",
REPT(
" ",
200)),
{1,
2,
3,
4}*200,
200))*{-1,
1,
-1,
1})Excel solution 2 for Clean and Sum a Text String, proposed by محمد حلمي:
=SUM(
IFERROR(
INDEX(
MID(
e,
FIND(
"/",
e)+1,
3)-LEFT(
e,
FIND(
"/",
e)-1),
,
1),
))Excel solution 3 for Clean and Sum a Text String, proposed by Kris Jaganah:
=MAP(
Table13[Visits Tracker],
LAMBDA(
x,
SUM(
MMULT(
-TEXTSPLIT(
UPPER(
x),
"/",
CHAR(
VSTACK(
SEQUENCE(
26,
,
65),
32,
45)),
1,
,
0),
{1;-1}))))Excel solution 4 for Clean and Sum a Text String, proposed by Hussein SATOUR:
=MAP(
B3:B6,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
" "),
SUM(
TEXTAFTER(
a,
"/",
,
,
,
0)-TEXTBEFORE(
a,
"/",
,
,
,
0)))))Excel solution 5 for Clean and Sum a Text String, proposed by Hussein SATOUR:
=-SUM(
IF(
ISNUMBER(
FILTERXML(
"<y><z>"&SUBSTITUTE(
SUBSTITUTE(
B3,
"/",
" -"),
" ",
"</z><z>")&"</z></y>",
"//z")),
FILTERXML(
"<y><z>"&SUBSTITUTE(
SUBSTITUTE(
B3,
"/",
" -"),
" ",
"</z><z>")&"</z></y>",
"//z"),
0))Excel solution 6 for Clean and Sum a Text String, proposed by JvdV –:
=-SUM(
FILTERXML(
"<t><s>"&SUBSTITUTE(
SUBSTITUTE(
B3,
"/",
" -"),
" ",
"</s><s>")&"</s></t>",
"//s[.*0=0]"))Excel solution 7 for Clean and Sum a Text String, proposed by Mey Tithveasna:
=MAP(
B3:B6,
LAMBDA(
b,
LET(
t,
TEXTSPLIT(
b,
" "),
SUM(
IFERROR(
TEXTAFTER(
t,
"/")-TEXTBEFORE(
t,
"/"),
0)))))Excel solution 8 for Clean and Sum a Text String, proposed by Edwin Tisnado:
=MAP(
B3:B6,
LAMBDA(
x,
SUM(
TOCOL(
TEXTSPLIT(
x,
"/",
VSTACK(
CHAR(
SEQUENCE(
26,
,
65)),
"-"),
1,
1)*{-1,
1},
2))))Solving the challenge of Clean and Sum a Text String with Python in Excel
Python in Excel solution 1 for Clean and Sum a Text String, proposed by JvdV –:
=PY(
import re
[eval(re.sub(r'D*(d+)/(d+)', r'-1+2', s)) for s in xl("B3:B6")[0]]
)
[eval(s) for s in xl("B3:B6").replace(r'D*(d+)/(d+)', r'-1+2', regex=True)[0].values]