Extract the numbers and sum them along with their signs. Sign can appear either before or after number. If no sign, number will need to be treated as positive. Ex. abc 45+ dfjfj 34- ty 84 op -12 = 45-34+84-12 = 83
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 529
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Signed Number String Sum with Power Query
Power Query solution 1 for Signed Number String Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Remove([Strings], {"A" .. "Z", "a" .. "z"}),
b = List.Select(Text.Split(a, " "), each _ <> ""),
c = List.Transform(
b,
each Number.From(
if Text.Contains(_, "-") then ("-" & Text.Remove(_, "-")) else Text.Remove(_, "+")
)
),
d = List.Sum(c) ?? 0
in
d
)[[Answer]]
in
SolPower Query solution 2 for Signed Number String Sum, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"My Answer",
each [
a = Text.SplitAny([Strings], Text.Combine({"A" .. "Z", "a" .. "z", " "})),
b = List.Select(a, each _ <> ""),
c = List.Transform(
b,
each Number.From(
if Text.Contains(_, "-") then "-" & Text.Replace(_, "-", "") else Text.Replace(_, "+", "")
)
),
d = List.Sum(c) ?? 0
][d]
)
in
ResultPower Query solution 3 for Signed Number String Sum, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
L = List.Transform,
a = Text.Split(Text.Select([Strings], {"0" .. "9", " ", "-", "+"}), " "),
b = List.Select(a, each _ <> ""),
c = L(
b,
each
if Text.EndsWith(_, "+") or Text.EndsWith(_, "-") then
Text.Insert(_, 0, Text.End(_, 1))
else
_
),
d = L(
c,
each
if Text.End(_, 1) = "+" or Text.End(_, 1) = "-" then
Text.RemoveRange(_, Text.Length(_) - 1, 1)
else
_
),
e = L(d, each Text.Remove(_, "+")),
f = List.Sum(L(e, Number.From)),
g = if f = null then 0 else f
in
g
)
in
SolPower Query solution 4 for Signed Number String Sum, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Strings", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"NumList1",
each List.RemoveItems(
Text.Split(Text.Remove([Strings], {"A" .. "Z", "a" .. "z", "+"}), " "),
{""}
)
),
#"Expanded NumList1" = Table.ExpandListColumn(#"Added Custom", "NumList1"),
#"Added Custom1" = Table.AddColumn(
#"Expanded NumList1",
"Num2",
each try
if Text.Contains([NumList1], "-") then "-" & Text.Remove([NumList1], "-") else [NumList1]
otherwise
0
),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1", {{"Num2", Int64.Type}}),
#"Grouped Rows" = Table.Group(
#"Changed Type1",
{"Strings"},
{{"Answer", each List.Sum([Num2]), type nullable number}}
)
in
#"Grouped Rows"Power Query solution 5 for Signed Number String Sum, proposed by Chris A.:
Solution- Add a column in Power Query and paste this:
List.Accumulate(
List.Transform(
List.Select(
Text.Split(Text.Select([Strings], List.Union({{"-", "+", " "}, {"0" .. "9"}})), " "),
each Text.Length(_) > 1
),
each
if Text.EndsWith(_, "+") or Text.EndsWith(_, "-") then
Text.End(_, 1) & Text.Start(_, Text.Length(_) - 1)
else
_
),
0,
(state, current) => Number.From(state) + Number.From(current)
)
Solving the challenge of Signed Number String Sum with Excel
Excel solution 1 for Signed Number String Sum, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(n,REGEXEXTRACT(a,"[-d]+",1),IFNA(SUM((2*ISERR(FIND("-",n))-1)*SUBSTITUTE(n,"-",)),))))Excel solution 2 for Signed Number String Sum, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(n,TEXTSPLIT(a,{" ","+"},CHAR(SEQUENCE(26,,65)),1,1),IFERROR(SUM((2*ISERR(FIND("-",n))-1)*SUBSTITUTE(n,"-",)),))))Excel solution 3 for Signed Number String Sum, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(f,LAMBDA([d],TEXTSPLIT(0&x,CHAR(VSTACK(32,ROW(65:90),IF(d,1,{43;45}))),,1,1)),SUM(-IF(ISERR(FIND("-",f(1))),-f(),f())))))Excel solution 4 for Signed Number String Sum, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
i,
TRIM(
TEXTSPLIT(
a,
CHAR(
SEQUENCE(
150
)+64
),
"+"
)
),
SUM(
IFERROR(
--IF(
RIGHT(
i
)="-",
-TEXTSPLIT(
i,
"-"
),
i
),
)
)
)
)
)Excel solution 5 for Signed Number String Sum, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(x,LET(a,IFNA(REGEXEXTRACT(x,"[0-9-]+",1),0),SUM(IFERROR(--a,-TEXTBEFORE(a,"-"))))))Excel solution 6 for Signed Number String Sum, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
IFNA(
REGEXEXTRACT(
x,
"[0-9+-]+",
1
),
0
),
SUM(
IFERROR(
--SUBSTITUTE(
a,
"+",
""
),
-TEXTBEFORE(
a,
"-"
)
)
)
)
)
)Excel solution 7 for Signed Number String Sum, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
IFNA(
SUM(
--BYCOL(
VSTACK(
TRIM(
BYCOL(
IFNA(
WRAPCOLS(
REGEXEXTRACT(
z,
"(?<=d)[ +-]|[ +-](?=d)",
1
),
2
),
""
),
LAMBDA(
x,
CONCAT(
x
)
)
)
),
REGEXEXTRACT(
z,
"d+",
1
)
),
LAMBDA(
x,
CONCAT(
x
)
)
)
),
0
)
)
)Excel solution 8 for Signed Number String Sum, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
VSTACK(
UNICHAR(
SEQUENCE(
26,
,
97
)
),
{" "}
),
e,
TEXTSPLIT(
a,
,
d,
,
1
),
f,
FILTER(
e,
e<>""
),
g,
IF(
SUBSTITUTE(
f,
"+",
""
)<>f,
--SUBSTITUTE(
f,
"+",
""
),
0
),
h,
IF(
SUBSTITUTE(
f,
"-",
""
)<>f,
-SUBSTITUTE(
f,
"-",
""
),
0
),
i,
IF(
g=h,
--f,
0
),
IFERROR(
SUM(
g,
h,
i
),
0
)
)
)
)Excel solution 9 for Signed Number String Sum, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
LAMBDA(a,
LET(t,
TEXTSPLIT(
a,
" ",
CHAR(
ROW(
65:90
)
),
1,
1
),
IFERROR(SUM(-IFERROR(-t,
-(RIGHT(
t
)&LEFT(
t,
LEN(
t
)-1
)))),
0))))Excel solution 10 for Signed Number String Sum, proposed by Sunny Baggu:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
x,
LET(
_a,
TEXTSPLIT(
UPPER(
x
),
,
VSTACK(
CHAR(
32
),
CHAR(
SEQUENCE(
26,
,
65
)
)
),
1
),
_b,
SUBSTITUTE(
SUBSTITUTE(
_a,
"+",
""
),
"-",
""
) + 0,
_c,
ISNUMBER(
SEARCH(
"-",
_a
)
),
_d,
IF(
_c,
-_b,
_b
),
SUM(
_d
)
)
)
),
0
)Excel solution 11 for Signed Number String Sum, proposed by Pieter de B.:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
IFERROR(
TEXTSPLIT(
UPPER(
a
),
CHAR(
VSTACK(
32,
SEQUENCE(
26,
,
65
)
)
),
,
1
),
0
),
c,
-TEXTSPLIT(
b,
{"+";"-"},
,
1
),
SUM(
IF(
ISERR(
FIND(
"-",
b
)
),
-c,
c
)
)
)
)
)Excel solution 12 for Signed Number String Sum, proposed by Asheesh Pahwa:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
UPPER(
x
),
CHAR(
32
),
CHAR(
SEQUENCE(
26,
,
65
)
),
1
),
s,
SUBSTITUTE(
SUBSTITUTE(
t,
"+",
""
),
"-",
""
)*1,
i,
ISNUMBER(
FIND(
"-",
t
)
),
sm,
IFERROR(
SUM(
IF(
i,
-s,
s
)
),
0
),
sm
)
)
)Excel solution 13 for Signed Number String Sum, proposed by Andy Heybruch:
=MAP(
A2:A10,
LAMBDA(
_string,
LET(
_a,
MID(
_string,
SEQUENCE(
LEN(
_string
)
),
1
),
_n,
TRIM(
CONCAT(
IFERROR(
IF(
FIND(
_a,
"-0123456789"
),
_a
),
& " "
)
)
),
_x,
TEXTSPLIT(
_n,
" "
),
IFERROR(
SUM(
IF(
RIGHT(
_x
)="-",
-LEFT(
_x,
LEN(
_x
)-1
),
--_x
)
),
""
)
)
)
)Excel solution 14 for Signed Number String Sum, proposed by JvdV -:
=MAP(A2:A10,LAMBDA(s,SUM(--TEXTSPLIT(REGEXREPLACE(0&s,"(-?d+)(-)?|.","$2$1 ")," ",,1))))Excel solution 15 for Signed Number String Sum, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(
A2:A10,
LAMBDA(
i,
LET(
g,
TEXTSPLIT(
i,
,
" "
),
w,
IFERROR(
MAP(
g,
LAMBDA(
z,
LET(
y,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
TEXTJOIN(
,
,
FILTER(
y,
NOT(
ISNUMBER(
SEARCH(
"true",
MAP(
y,
LAMBDA(
x,
TEXTJOIN(
,
,
UNIQUE(
x=CHAR(
ROW(
A65:A90
)
)
)
)
)
)
)
)
)
)
)
)
)
),
""
),
q,
LET(
v,
IFERROR(
MAP(
g,
LAMBDA(
z,
LET(
y,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
TEXTJOIN(
,
,
FILTER(
y,
NOT(
ISNUMBER(
SEARCH(
"true",
MAP(
y,
LAMBDA(
x,
TEXTJOIN(
,
,
UNIQUE(
x=CHAR(
ROW(
A65:A90
)
)
)
)
)
)
)
)
)
)
)
)
)
),
""
),
FILTER(
v,
v<>""
)
),
SUM(
VALUE(
VSTACK(
IFERROR(
"+"&SUBSTITUTE(
FILTER(
w,
ISNUMBER(
SEARCH(
"+",
w
)
)
),
"+",
""
),
0
),
IFERROR(
"-"&SUBSTITUTE(
FILTER(
w,
ISNUMBER(
SEARCH(
"-",
w
)
)
),
"-",
""
),
0
),
IFERROR(
FILTER(
q,
BYROW(
IFERROR(
SEARCH(
{"-",
"+"},
q
),
0
),
LAMBDA(
o,
SUM(
o
)
)
)=0
),
0
)
)
)
)
)
)
)Excel solution 16 for Signed Number String Sum, proposed by Imam Hambali:
=LET(
a,
DROP(
REDUCE(
"",
A2:A10,
LAMBDA(
x,
y,
VSTACK(
x,
IFNA(
SUBSTITUTE(
REGEXEXTRACT(
y,
"[-+]?d+[-+]?",
1
),
"+",
""
),
0
)
)
)
),
1
),
BYROW(
IFNA(
IF(
ISNUMBER(
FIND(
"-",
a
)
),
SUBSTITUTE(
a,
"-",
""
)*-1,
a*1
),
0
),
SUM
)
)Excel solution 17 for Signed Number String Sum, proposed by Eddy Wijaya:
=LET(
f,LAMBDA(a,MID(a,SEQUENCE(LEN(a)),1)),
l_str,A2:A10,
DROP(REDUCE(0,l_str,LAMBDA(a,v,VSTACK(a,
IFERROR(LET(
split,f(v),
extract,TEXTJOIN("",,FILTER(split,CODE(split)<58)),
DROP(REDUCE(0,extract,LAMBDA(a,v,VSTACK(a,
LET(
split_2,TEXTSPLIT(v,," "),
SUM(--TOCOL(BYROW(split_2,LAMBDA(r,LET(op,RIGHT(r,1),IF(ISNUMBER(--op),r,op&LEFT(r,LEN(r)-1))))),2)))))),1)),0)))),1))Excel solution 18 for Signed Number String Sum, proposed by Edwin Tisnado:
=MAP(A2:A10,LAMBDA(r,LET(f,LAMBDA(x,--IF(RIGHT(x)="-",-SUBSTITUTE(x,"-",),x)),SUM(IFERROR(f(TRIM(SUBSTITUTE(TEXTSPLIT(r,CHAR(SEQUENCE(27)+64),,1,1),"+",))),0)))))Excel solution 19 for Signed Number String Sum, proposed by El Badlis Mohd Marzudin:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
REGEXEXTRACT(
x,
"[0-9/-]+",
1
),
IFERROR(
SUM(
IF(
IFERROR(
FIND(
"-",
a
),
0
),
SUBSTITUTE(
a,
"-",
""
)*-1,
a+0
)
),
0
)
)
)
)Excel solution 20 for Signed Number String Sum, proposed by Anup Kumar:
=BYROW(A2:A10,LAMBDA(a,LET(
txt,SUBSTITUTE(LOWER(a)," ",""),
spt, TEXTSPLIT(txt,,UNICHAR(SEQUENCE(26,,97)),TRUE),
sgn, SCAN(0,spt,LAMBDA(x,y,IFS(RIGHT(y,1)="-",LEFT(y,LEN(y)-1)*-1,RIGHT(y,1)="+",("+"&LEFT(y,LEN(y)-1))*1,TRUE,1*y))),
IFERROR(SUM(sgn),0)
)))Excel solution 21 for Signed Number String Sum, proposed by Ricardo Alexis Domínguez Hernández:
=BYROW(
A2:A10,
LAMBDA(
c,
SUM(
IFERROR(
LET(
b,
TEXTSPLIT(
CONCAT(
MAP(
MID(
c,
SEQUENCE(
,
LEN(
c
)
),
1
),
LAMBDA(
x,
LET(
a,
CODE(
x
),
IF(
OR(
a=43,
a=45,
AND(
a>=48,
a<=59
)
),
x,
" "
)
)
)
)
),
" "
),
IF(
RIGHT(
b
)="+",
1*LEFT(
b,
LEN(
b
)-1
),
IF(
RIGHT(
b
)="-",
-1*LEFT(
b,
LEN(
b
)-1
),
b*1
)
)
),
0
)
)
)
)Excel solution 22 for Signed Number String Sum, proposed by Safa Macit:
=LET(
a,MID(A2,MAKEARRAY(1,LEN(A2),LAMBDA(r,c,c)),1),
b,SWITCH(TRUE,a="-",a,a="+",a,a=" ",";",IFERROR(NUMBERVALUE(a),"X")),
c,TEXTSPLIT(TEXTJOIN(,,FILTER(b,b<>"X")),";"),
SUM(IF(ISNUMBER(SEARCH("-",c)),NUMBERVALUE(SUBSTITUTE(c,"-",""))*-1,NUMBERVALUE(SUBSTITUTE(c,"+","")))))
or for whole range
=MAP(A2:A10,LAMBDA(x,
LET(
a,MID(x,MAKEARRAY(1,LEN(x),LAMBDA(r,c,c)),1),
b,SWITCH(TRUE,a="-",a,a="+",a,a=" ",";",IFERROR(NUMBERVALUE(a),"X")),
c,TEXTSPLIT(TEXTJOIN(,,FILTER(b,b<>"X")),";"),
SUM(IF(ISNUMBER(SEARCH("-",c)),NUMBERVALUE(SUBSTITUTE(c,"-",""))*-1,NUMBERVALUE(SUBSTITUTE(c,"+","")))))))Excel solution 23 for Signed Number String Sum, proposed by Jeremy Freelove:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
elements,
TEXTSPLIT(
v,
,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
" "
),
1,
1
),
values,
BYROW(
elements,
LAMBDA(
r,
VALUE(
INDEX(
TEXTSPLIT(
r,
{"+",
"-",
" "},
,
1
),
1
)
)
)
),
signs,
BYROW(
elements,
LAMBDA(
r,
VALUE(
INDEX(
TEXTSPLIT(
r,
CHAR(
SEQUENCE(
10,
,
48
)
)
),
1
)&1
)
)
),
IFERROR(
SUMPRODUCT(
values,
signs
),
0
)
)
)
)Solving the challenge of Signed Number String Sum with Python
Python solution 1 for Signed Number String Sum, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
pattern = r"(?<=D)[+-]?d+[+-]?"
path = "529 Sum with Signs.xlsx"
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B")
def adjust_numbers(s):
numbers = re.findall(pattern, s)
adjusted_numbers = [int(num[:-1]) if num.endswith('+') else -int(num[:-1]) if num.endswith('-') else int(num) for num in numbers]
return sum(adjusted_numbers)
input = input.map(adjust_numbers)
print(input["Strings"].equals(test["Answer Expected"])) # True
Solving the challenge of Signed Number String Sum with Python in Excel
Python in Excel solution 1 for Signed Number String Sum, proposed by Alejandro Campos:
import re
pattern = r[d+-]+"
data = xl("A2:A10")[0]
def extract_numbers_with_sing(s):
numbers = re.findall(pattern, s)
numbers_with_sing = []
for num in numbers:
if num.endswith('+'):
numbers_with_sing.append(int(num[:-1]))
elif num.endswith('-'):
numbers_with_sing.append(-int(num[:-1]))
else:
numbers_with_sing.append(int(num))
return sum(numbers_with_sing)
result = [extract_numbers_with_sing(s) for s in data]
res = pd.DataFrame(result, columns=['Answer'])
res
Solving the challenge of Signed Number String Sum with R
R solution 1 for Signed Number String Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
pattern <- "(?<=\D)[+-]?\d+[+-]?"
path = "Excel/529 Sum with Signs.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
result = input %>%
mutate(numbers = str_extract_all(Strings, pattern)) %>%
mutate(adj_num = map_dbl(numbers,
~sum(as.numeric(str_replace_all(.x, "[+-]", "")) *
ifelse(str_detect(.x, "-"), -1, 1)), na.rm = TRUE))
identical(result$adj_num, test$`Answer Expected`)
#> [1] TRUE
&&