Extract the unique positive and negative numbers if they are immediately preceded by either plus or minus sign. Ex. 5Q8Y+6R-34L12 Only 6 is preceded by + and 34 is preceded by –
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 383
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Signed Numbers from Text with Power Query
Power Query solution 1 for Extract Signed Numbers from Text, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
z = List.Transform(
{"+", "-"},
(y) =>
let
a = List.Skip(Text.Split([Strings], y)),
b = List.Transform(
{0 .. List.Count(a) - 1},
each List.Max(
List.Transform(
{1 .. Text.Length(a{_})},
(x) => try Number.From(Text.Range(a{_}, 0, x)) otherwise null
)
)
),
c = Text.Combine(List.Transform(List.Distinct(b), Text.From), ", ")
in
c
)
in
Table.FromRows({z}, {"Positive", "Negative"})
)[A]
)
in
SolPower Query solution 2 for Extract Signed Numbers from Text, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Splitter.SplitTextByCharacterTransition(
{"A" .. "Z", "0" .. "9", "+", "-"},
{"+", "-", "A" .. "Z"}
)([Strings]),
b = List.Distinct(
List.Select(
a,
each (Text.Start(_, 1) = "+" or Text.Start(_, 1) = "-") and Text.Length(_) <> 1
)
),
pos = Text.Combine(
List.Transform(List.Select(b, each Text.Start(_, 1) = "+"), each Text.Remove(_, "+")),
", "
),
neg = Text.Combine(
List.Transform(List.Select(b, each Text.Start(_, 1) = "-"), each Text.Remove(_, "-")),
", "
)
][[pos], [neg]]
),
res = Table.ExpandRecordColumn(tab, "Personalizar", {"pos", "neg"})
in
resSolving the challenge of Extract Signed Numbers from Text with Excel
Excel solution 1 for Extract Signed Numbers from Text, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A3:A10&"-+",
MAKEARRAY(
ROWS(
z
),
2,
LAMBDA(
r,
c,
TEXTJOIN(
", ",
,
UNIQUE(
TOCOL(
DROP(
TEXTSPLIT(
INDEX(
z,
r,
1
),
CHAR(
41+2*c
),
VSTACK(
CHAR(
47-2*c
),
CHAR(
SEQUENCE(
26
)+64
)
),
,
,
),
,
1
)
)
)
)
)
)
)Excel solution 2 for Extract Signed Numbers from Text, proposed by John V.:
=DROP(
REDUCE(
0,
A3:A10,
LAMBDA(
a,
v,
LET(
f,
LAMBDA(
s,
LET(
n,
UNIQUE(
BYCOL(
DROP(
IFERROR(
--LEFT(
TEXTSPLIT(
v&s,
s
),
ROW(
1:15
)
),
),
,
1
),
MAX
),
1
),
TEXTJOIN(
", ",
,
IF(
n,
n,
""
)
)
)
),
VSTACK(
a,
HSTACK(
f(
"+"
),
f(
"-"
)
)
)
)
)
),
1
)Excel solution 3 for Extract Signed Numbers from Text, proposed by محمد حلمي:
=LET(
r,
LAMBDA(
x,
v,
MAP(
A3:A10,
LAMBDA(
a,
ARRAYTOTEXT(
UNIQUE(
IFERROR(
TOCOL(
TEXTSPLIT(
DROP(
TEXTSPLIT(
a,
x,
CHAR(
SEQUENCE(
26
)+64
)
),
,
1
),
v
),
2
),
""
)
)
)
)
)
),
HSTACK(
r(
"+",
"-"
),
r(
"-",
"+"
)
)
)Excel solution 4 for Extract Signed Numbers from Text, proposed by Kris Jaganah:
=REDUCE(
{"Positive Numbers",
"Negative Numbers"},
A3:A10,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
CHAR(
SEQUENCE(
26,
,
65
)
),
b,
IFERROR(
DROP(
TEXTSPLIT(
y,
,
"+"
),
1
),
""
),
c,
TEXTJOIN(
", ",
,
UNIQUE(
TEXTBEFORE(
b,
VSTACK(
"-",
a
),
,
,
,
b
)
)
),
d,
IFERROR(
DROP(
TEXTSPLIT(
y,
,
"-"
),
1
),
""
),
e,
TEXTJOIN(
", ",
,
UNIQUE(
TEXTBEFORE(
d,
VSTACK(
"+",
a
),
,
,
,
d
)
)
),
HSTACK(
c,
e
)
)
)
)
)Excel solution 5 for Extract Signed Numbers from Text, proposed by Julian Poeltl:
=LET(STRY,
A3:A10,
HSTACK(BYROW(STRY,
LAMBDA(string,
TEXTJOIN(", ",
TRUE,
UNIQUE(IFERROR(BYCOL(TEXTSPLIT(
"A"&string,
"+"
),
LAMBDA(array,
DROP(TEXTSPLIT(
TEXTSPLIT(
TEXTSPLIT(
array,
CHAR(
SEQUENCE(
26
)+64
)
),
"+"
),
"-"
),
0,
(COUNTA(
TEXTSPLIT(
TEXTSPLIT(
TEXTSPLIT(
array,
CHAR(
SEQUENCE(
26
)+64
)
),
"+"
),
"-"
)
)-1)*-1))),
""),
TRUE)))),
BYROW(STRY,
LAMBDA(string,
TEXTJOIN(", ",
TRUE,
UNIQUE(IFERROR(BYCOL(TEXTSPLIT(
"A"&string,
"-"
),
LAMBDA(array,
DROP(TEXTSPLIT(
TEXTSPLIT(
TEXTSPLIT(
array,
CHAR(
SEQUENCE(
26
)+64
)
),
"+"
),
"-"
),
0,
(COUNTA(
TEXTSPLIT(
TEXTSPLIT(
TEXTSPLIT(
array,
CHAR(
SEQUENCE(
26
)+64
)
),
"+"
),
"-"
)
)-1)*-1))),
""),
TRUE))))))Excel solution 6 for Extract Signed Numbers from Text, proposed by Timothée BLIOT:
=REDUCE(
{"Positive Numbers",
"Negative Numbers"},
A3:A10,
LAMBDA(
w,
v,
LET(
F,
LAMBDA(
x,
IFNA(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
REGEXEXTRACT(
v,
x,
1
)
)
)
),
""
)
),
VSTACK(
w,
HSTACK(
F(
"(?<=+)d+"
),
F(
"(?<=-)d+"
)
)
)
)
)
)Excel solution 7 for Extract Signed Numbers from Text, proposed by Hussein SATOUR:
=LET(
F,
LAMBDA(
x,
y,
LET(
a,
TEXTAFTER(
x,
y,
SEQUENCE(
5
)
),
b,
IFERROR(
TEXTBEFORE(
a,
{"+",
"-"}
),
a
),
c,
--VSTACK(
b,
TEXTBEFORE(
b,
CHAR(
SEQUENCE(
26
)+64
)
)
),
ARRAYTOTEXT(
UNIQUE(
FILTER(
c,
NOT(
ISERROR(
c
)
)
)
)
)
)
),
TEXTSPLIT(
CONCAT(
MAP(
A3:A10,
LAMBDA(
z,
IFERROR(
F(
z,
"+"
)&"/"&F(
z,
"-"
),
"*/*"
)&"|"
)
)
),
"/",
"|",
1
)
)Excel solution 8 for Extract Signed Numbers from Text, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
v,
MAP(
A3:A10,
LAMBDA(
x,
ARRAYTOTEXT(
UNIQUE(
TOCOL(
MAP(
DROP(
TEXTSPLIT(
x,
,
v
),
1
),
LAMBDA(
x,
LOOKUP(
69^96,
--LEFT(
x,
ROW(
1:20
)
)
)
)
),
3
)
)
)
)
)
),
IFERROR(
HSTACK(
f(
"+"
),
f(
"-"
)
),
""
)
)Excel solution 9 for Extract Signed Numbers from Text, proposed by Sunny Baggu:
=LET(
_e1,
LAMBDA(
x,
y,
z,
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
TEXTBEFORE(
TEXTBEFORE(
DROP(
TEXTSPLIT(
x,
,
y
),
1
),
CHAR(
SEQUENCE(
26,
,
CODE(
"A"
)
)
),
,
& ,
1,
""
),
z,
,
,
1
),
3
)
)
),
""
)
),
REDUCE(
{"Positive Numbers",
"Negative Numbers"},
A3:A10,
LAMBDA(a,
v,
VSTACK(a,
HSTACK(_e1(v,
"+",
"-"),
_e1(v,
"-",
"+"))))
)
)Excel solution 10 for Extract Signed Numbers from Text, proposed by Sunny Baggu:
=LET(
_e1,
LAMBDA(
x,
y,
z,
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
TEXTBEFORE(
TEXTBEFORE(
DROP(
TEXTSPLIT(
x,
,
y
),
1
),
CHAR(
SEQUENCE(
26,
,
CODE(
"A"
)
)
),
,
,
1,
""
),
z,
,
,
1
),
3
)
)
),
""
)
),
HSTACK(MAP(A3:A10,
LAMBDA(a,
_e1(a,
"+",
"-"))),
MAP(A3:A10,
LAMBDA(a,
_e1(a,
"-",
"+"))))
)Excel solution 11 for Extract Signed Numbers from Text, proposed by LEONARD OCHEA 🇷🇴:
=LET(F,
LAMBDA(x,
y,
LET(r,
DROP(
REDUCE(
"",
DROP(
TEXTSPLIT(
x,
y
),
,
1
),
LAMBDA(
a,
b,
HSTACK(
a,
--LEFT(
b,
SEQUENCE(
LEN(
b
)
)
)
)
)
),
,
1
),
TEXTJOIN(", ",
,
IFERROR(UNIQUE(1/(1/BYCOL(
IFERROR(
r,
),
MAX
)),
1),
"")))),
REDUCE(
B2:C2,
A3:A10,
LAMBDA(
i,
j,
VSTACK(
i,
HSTACK(
F(
j,
"+"
),
F(
j,
"-"
)
)
)
)
))Excel solution 12 for Extract Signed Numbers from Text, proposed by Abdallah Ally:
CHAR(SEQUENCE(26)+64) 🙌👏Excel solution 13 for Extract Signed Numbers from Text, proposed by Abdallah Ally:
=DROP(REDUCE("",
A3:A10,
LAMBDA(x,
y,
VSTACK(x,
LET(a,
y,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
TRIM(
TEXTSPLIT(
CONCAT(
MAP(
b,
LAMBDA(
x,
IFS(
OR(
UPPER(
x
)=CHAR(
SEQUENCE(
26,
,
65
)
)
),
" ",
ISNUMBER(
-x
),
x,
1,
" "&x
)
)
)
),
" ",
,
,
,
""
)
),
f,
LAMBDA(v,
s,
ARRAYTOTEXT(IFERROR(ABS(UNIQUE(FILTER(v,
(LEFT(
v
)=s)*(LEN(
v
)>1),
""),
1)),
""))),
HSTACK(
f(
c,
"+"
),
f(
c,
"-"
)
))))),
1)Excel solution 14 for Extract Signed Numbers from Text, proposed by Charles Roldan:
=REDUCE(
B2:C2,
A3:A10,
LET(
s,
{"+",
"-"},
c,
CHAR(
SEQUENCE(
,
26,
CODE(
"A"
)
)
),
LAMBDA(
_,
x,
VSTACK(
_,
MAP(
s,
LAMBDA(
y,
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
--TEXTSPLIT(
TEXTSPLIT(
"Thank you Excel BI" & x,
y
),
,
HSTACK(
s,
c
)
),
3
)
)
),
""
)
)
)
)
)
)
)Excel solution 15 for Extract Signed Numbers from Text, proposed by Bilal Mahmoud kh.:
=IFERROR(
TEXTSPLIT(
TEXTJOIN(
"|",
,
MAP(
A1:A8,
LAMBDA(
x,
LET(
n,
TEXTSPLIT(
x,
{"+",
"-"}
),
m,
TEXTJOIN(
" ",
,
MAP(
n,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
CONCAT(
IF(
ISNUMBER(
a*1
),
a,
" "
)
),
b
)
)
)
),
o,
TEXTSPLIT(
m,
" ",
,
TRUE
),
p,
IFERROR(
FIND(
"+"&o,
x,
1
),
""
),
q,
IFERROR(
FIND(
"-"&o,
x,
1
),
""
),
pos,
TEXTJOIN(
",",
TRUE,
UNIQUE(
TOCOL(
IFERROR(
MID(
x,
p+1,
LEN(
o
)
),
""
)
)
)
),
neg,
TEXTJOIN(
",",
TRUE,
UNIQUE(
TOCOL(
IFERROR(
MID(
x,
q+1,
LEN(
o
)
),
""
)
)
)
),
pos&"/"&neg
)
)
)
),
"/",
"|",
FALSE
),
""
)Excel solution 16 for Extract Signed Numbers from Text, proposed by Pieter de Bruijn:
=LET(x,
LAMBDA(y,
BYROW(MAP(TEXTAFTER(
A3:A10,
y,
SEQUENCE(
,
5
),
,
,
),
LAMBDA(m,
LET(n,
--MID(
m,
1,
10-SEQUENCE(
9
)
),
TAKE(TOCOL(n/(LEN(
n
)=10-SEQUENCE(
9
)),
2),
1)))),
LAMBDA(
r,
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
r,
2
)
)
),
""
)
))),
HSTACK(
x(
"+"
),
x(
"-"
)
))Excel solution 17 for Extract Signed Numbers from Text, proposed by Ziad A.:
=ARRAYFORMULA(
MAP(
REGEXREPLACE(
A3:A10,
{"+",
"-"}&"(d+)|.",
"$1 "
),
LAMBDA(
n,
JOIN(
", ",
UNIQUE(
SPLIT(
n,
" "
),
1
)
)
)
)
)Excel solution 18 for Extract Signed Numbers from Text, proposed by Edwin Tisnado:
=DROP(
REDUCE(
"",
A3:A10,
LAMBDA(
u,
v,
LET(
f,
LAMBDA(
x,
y,
TEXTJOIN(
",",
1,
IFERROR(
UNIQUE(
TOCOL(
DROP(
TEXTSPLIT(
TEXTSPLIT(
v,
x,
y
),
CHAR(
SEQUENCE(
26
)+64
)
),
,
1
),
3
)
),
""
)
)
),
VSTACK(
u,
HSTACK(
f(
"+",
"-"
),
f(
"-",
"+"
)
)
)
)
)
),
1
)Excel solution 19 for Extract Signed Numbers from Text, proposed by Anup Kumar:
=LET(
tx,
A3,
rp,
SUBSTITUTE(
tx,
"+",
"^+"
),
rm,
SUBSTITUTE(
rp,
"-",
"^-"
),
ts,
TOROW(
TEXTSPLIT(
rm,
"^",
UNICHAR(
SEQUENCE(
,
26,
65
)
),
TRUE,
1,
""
)
),
nu,
UNIQUE(
FILTER(
ts,
ts<>""
),
TRUE
),
pl,
TEXTJOIN(
", ",
,
TEXTAFTER(
FILTER(
nu,
LEFT(
nu,
1
)="+"
),
"+",
,
,
,
""
)
),
mn,
TEXTJOIN(
", ",
,
TEXTAFTER(
FILTER(
nu,
LEFT(
nu,
1
)="-"
),
"-",
,
,
,
""
)
),
HSTACK(
IFERROR(
pl,
""
),
IFERROR(
mn,
""
)
)
)Excel solution 20 for Extract Signed Numbers from Text, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(
f,
LAMBDA(
ce,
op,
LET(
t,
MID(
ce,
UNIQUE(
TOCOL(
SEARCH(
op,
ce,
SEQUENCE(
LEN(
ce
& )
)
),
3
)
),
100
),
e,
MAP(
t,
LAMBDA(
x,
LET(
n,
LEFT(
x,
SEQUENCE(
LEN(
x
)
)
),
MAX(
ABS(
FILTER(
n,
ISNUMBER(
--n
),
""
)
)
)
)
)
),
IFERROR(
ARRAYTOTEXT(
UNIQUE(
TOCOL(
e,
3
)
)
),
""
)
)
),
REDUCE(
B2:C2,
A3:A10,
LAMBDA(
a,
fi,
VSTACK(
a,
HSTACK(
f(
fi,
"+"
),
f(
fi,
"-"
)
)
)
)
)
)Excel solution 21 for Extract Signed Numbers from Text, proposed by Andres Rojas Moncada:
=LET(
fnr,
LAMBDA(
str,
sen,
pos,
neg,
fff,
IF(
str="",
pos&"/"&neg,
LET(
c,
LEFT(
str
),
esn,
ISNUMBER(
c*1
),
nstr,
RIGHT(
str,
LEN(
str
)-1
),
IF(
c="+",
fff(
nstr,
1,
pos&" ",
neg&" ",
fff
),
IF(
c="-",
fff(
nstr,
2,
pos&" ",
neg&" ",
fff
),
IF(
AND(
sen=1,
esn
),
fff(
nstr,
sen,
pos&c,
neg,
fff
),
IF(
AND(
sen=2,
esn
),
fff(
nstr,
sen,
pos,
neg&c,
fff
),
fff(
nstr,
0,
pos&" ",
neg&" ",
fff
)
)
)
)
)
)
)
),
f,
LAMBDA(
cad,
TEXTJOIN(
", ",
1,
UNIQUE(
TEXTSPLIT(
cad,
,
" "
)
)
)
),
DROP(
REDUCE(
"",
A3:A10,
LAMBDA(
a,
v,
LET(
res,
fnr(
v,
0,
"",
"",
fnr
),
VSTACK(
a,
HSTACK(
f(
TEXTBEFORE(
res,
"/"
)
),
f(
TEXTAFTER(
res,
"/"
)
)
)
)
)
)
),
1
)
)Solving the challenge of Extract Signed Numbers from Text with Python
Python solution 1 for Extract Signed Numbers from Text, proposed by Jan Willem Van Holst:
Long live Regex:
import pandas as pd
import re
df = pd.read_csv(r"C:JWLENOVOPYTHONPQ challengesExcel_Challenge_383.csv", sep=',', skipfooter=2)
string_input = df['Strings'].to_list()
plus_list = []
neg_list = []
for row in string_input:
plus = list(set([elem.replace('+', '') for elem in re.findall(r"+d+",row)]))
plus_str = ', '.join(plus)
plus_list.append(plus_str)
neg = list(set([elem.replace('-', '') for elem in re.findall(r"-d+",row)]))
neg_str = ', '.join(neg)
neg_list.append(neg_str)
df['+']=plus_list
df['-']=neg_list
print(df)
Solving the challenge of Extract Signed Numbers from Text with Python in Excel
Python in Excel solution 1 for Extract Signed Numbers from Text, proposed by JvdV -:
import regex as re
[[', '.join(re.findall(fr'{x}(d++)(?Solving the challenge of Extract Signed Numbers from Text with R
R solution 1 for Extract Signed Numbers from Text, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/383 Extract Positive and Negative Numbers.xlsx", range = "A2:A10")
test = read_excel("Excel/383 Extract Positive and Negative Numbers.xlsx", range = "B2:C10")
extract = function(input, sign) {
numbers = input %>%
str_extract_all(paste0(sign, "(\d+)")) %>%
unlist() %>%
as.numeric() %>%
abs() %>%
unique() %>%
str_c(collapse = ", ")
if (numbers == "") {
numbers = NA_character_
} else {
numbers = numbers
}
}
result = input %>%
mutate(positive = map_chr(Strings, extract, "\+"),
negative = map_chr(Strings, extract, "\-"))
&