Extract the first and last set of numbers appearing in A2:A10 and sum them.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 544
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of First and Last Number Sum with Power Query
Power Query solution 1 for First and Last Number Sum, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Ans",
each
let
a = Text.Combine(List.Transform({1 .. 47, 58 .. 255}, Character.FromNumber)),
b = Text.SplitAny([Strings], a),
c = List.RemoveNulls(List.Transform(b, Number.From)),
d = List.First(c),
e = if List.Count(c) < 2 then {d} else {d, List.Last(c)}
in
e
),
C = List.Sum(List.Combine(B[Ans]))
in
CPower Query solution 2 for First and Last Number Sum, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = List.Sum(
List.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = Text.Remove([Strings], List.Transform({0 .. 9}, Text.From)),
b = List.Accumulate(Text.ToList(a), [Strings], (s, c) => Text.Replace(s, c, " ")),
c = List.Select(Text.Split(b, " "), each _ <> ""),
d = List.Transform(
{c{0}? ?? null, if List.Count(c) > 1 then List.Last(c) else null},
Number.From
)
in
d
)[A]
)
)
in
SolPower Query solution 3 for First and Last Number Sum, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(
Fonte,
"sum",
each
let
a = Splitter.SplitTextByCharacterTransition(
{":" .. "z", "!" .. "-", " ", "0" .. "9"},
{":" .. "z", " "}
)([Strings]),
b = List.TransformMany(a, each {Text.Select(_, {"0" .. "9"})}, (a, b) => Number.From(b)),
c = List.RemoveNulls(b),
d = if List.Count(c) = 1 then List.First(c) else List.Sum({List.First(c), List.Last(c)})
in
d
)[sum],
res = List.Sum(add)
in
resPower Query solution 4 for First and Last Number Sum, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = Table.AddColumn(
Source,
"Result",
each [
a = Text.ToList([Strings]),
b = List.Transform(a, each if List.Contains({"0" .. "9"}, _) then _ else " "),
c = List.Transform(Text.Split(Text.Combine(b), " "), each try Number.From(_) otherwise null),
d = List.RemoveNulls(c),
e = if List.Count(d) > 1 then d{0} + List.Last(d) else d{0}? ?? 0
][e]
),
Result = List.Sum(Transform[Result])
in
ResultPower Query solution 5 for First and Last Number Sum, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
LT = List.Transform,
a = Text.ToList(x),
b = LT(a, each try Number.From(_) otherwise "X"),
c = LT(b, each if _ = null then "X" else _),
d = Text.Combine(LT(c, Text.From)),
e = Splitter.SplitTextByCharacterTransition({"A" .. "z"}, {"0" .. "9"})(d),
f = List.Combine(LT(e, Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"A" .. "z"}))),
g = LT(f, each try Number.From(_) otherwise null),
h = List.Select(g, each _ is number),
i = if List.Count(h) > 1 then List.First(h) + List.Last(h) else List.Last(h),
j = if i = null then 0 else i
in
j,
k = List.Sum(Table.AddColumn(S, "A", each Fx([Strings]))[A]),
Sol = Table.FromValue(k, [DefaultColumnName = "Answer Expected"])
in
SolPower Query solution 6 for First and Last Number Sum, proposed by Ahmed Ariem:
let
f = (x) =>
[
lst = List.Transform({33 .. 47} & {58 .. 126} & {32}, Character.FromNumber),
a = List.Transform(
List.RemoveMatchingItems(Splitter.SplitTextByAnyDelimiter(lst)(x), {""}),
Number.From
),
b = a{0}? + (if List.Count(a) > 1 then List.LastN(a, 1){0} else 0)
][b],
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
to = Table.FromValue(
List.Sum(Table.TransformColumns(Source, {"Strings", f})[Strings]),
[DefaultColumnName = "Answer Expected"]
)
in
toPower Query solution 7 for First and Last Number Sum, proposed by Ahmed Ariem:
let
f = (x) =>
[
lst = Splitter.SplitTextByAnyDelimiter(Text.ToList(Text.Remove(x, {"0" .. "9"})))(x),
a = List.Transform(List.RemoveMatchingItems(lst, {""}), Number.From),
b = a{0}? + (if List.Count(a) > 1 then List.LastN(a, 1){0} else 0)
][b],
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
to = Table.FromValue(
List.Sum(Table.TransformColumns(Source, {"Strings", f})[Strings]),
[DefaultColumnName = "Answer Expected"]
)
in
toSolving the challenge of First and Last Number Sum with Excel
Excel solution 1 for First and Last Number Sum, proposed by Bo Rydobon 🇹🇭:
=SUM(IFNA(--REGEXEXTRACT(A2:A10,"d+(?=D"&{"+d)","*$)"}),))Excel solution 2 for First and Last Number Sum, proposed by Bo Rydobon 🇹🇭:
=SUM(
MAP(
A2:A10,
LAMBDA(
v,
LET(
n,
IFERROR(
--TEXTSPLIT(
v,
,
TEXTSPLIT(
v,
SEQUENCE(
10
)-1,
,
1
),
1
),
0
),
@n+IF(
ROWS(
n
)>1,
TAKE(
n,
-1
)
)
)
)
)
)Excel solution 3 for First and Last Number Sum, proposed by John V.:
=SUM(MAP(A2:A10,LAMBDA(x,LET(n,IFNA(--REGEXEXTRACT(x,"d+",1),),IF(COUNT(n)=1,n,SUM(TAKE(n,,{1;-1})))))))
or
✅=SUM(IFNA(--REGEXEXTRACT(A2:A10,{"d+","d.*D(d+)"},{1,2}),))Excel solution 4 for First and Last Number Sum, proposed by Kris Jaganah:
=SUM(MAP(A2:A10,LAMBDA(x,LET(a,IFNA(--REGEXEXTRACT(x,"[0-9]+",1),0),b,TAKE(a,,1),IF(COUNT(a)>1,b+TAKE(a,,-1),b)))))Excel solution 5 for First and Last Number Sum, proposed by Timothée BLIOT:
=SUM(
IFNA(
MAP(
A2:A10,
LAMBDA(
z,
SUM(
--REGEXEXTRACT(
z,
"(?<=^D{,9})d+ |d+(?=D*$)",
1
)
)
)
),
0
)
)
v2: =SUM(
IFNA(
--REGEXEXTRACT(
A2:A10,
{"d+(?=D+d)",
"d+(?=D*$)"},
1
),
0
)
)Excel solution 6 for First and Last Number Sum, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=SUM(MAP(A2:A10, LAMBDA(a, LET(d, MID(a, SEQUENCE(LEN(a),,,1),1), e, REDUCE("",d, LAMBDA(x, y, IF(ISNUMBER(--y), x&y, x&" "))), f, TEXTSPLIT(e,, " "), g, FILTER(f, f<>""), IFERROR(IFS(ROWS(g)>1, TAKE(g,1)+TAKE(g,-1), ROWS(g)=1, 0+TAKE(g,1)),0)))))Excel solution 7 for First and Last Number Sum, proposed by Hussein SATOUR:
=SUM(MAP(A2:A10,LAMBDA(x,LET(a,--REGEXEXTRACT(x,"d+",1),IFERROR(IF(COUNT(a)=1,a,@a+TAKE(a,,-1)),0)))))Excel solution 8 for First and Last Number Sum, proposed by Oscar Mendez Roca Farell:
=SUM(N(MAP(A2:A10,
LAMBDA(a,
LET(t,
--TEXTSPLIT(
a,
,
TEXTSPLIT(
a,
ROW(
1:10
)-1,
,
1
),
1
),
IFERROR(@t+(ROWS(
t
)>1)*TAKE(
t,
-1
),
))))))Excel solution 9 for First and Last Number Sum, proposed by Sunny Baggu:
=SUM(
TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
_a,
TEXTSPLIT(
x,
TEXTSPLIT(
x,
SEQUENCE(
10,
,
0
),
,
1
),
,
1
) + 0,
IF(
COLUMNS(
_a
) = 1,
_a,
SUM(
TAKE(
_a,
,
{1,
-1}
)
)
)
)
)
),
3
)
)Excel solution 10 for First and Last Number Sum, proposed by Abdallah Ally:
=SUM(MAP(A2:A10,LAMBDA(x,LET(a,--IFNA(REGEXEXTRACT(x, "d+",1),0),IF(COUNT(a)>1,SUM(CHOOSECOLS(a,{1,-1})),a)))))Excel solution 11 for First and Last Number Sum, proposed by Hamidi Hamid:
=LET(w,TOCOL(REDUCE(,A2:A10,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," ",,)))),3),r,CONCAT(w),z,TOCOL(MID(r,SEQUENCE(LEN(r)),1)),e,TOCOL(IF(ISERROR(z*1),z,1/0),3),m,MAP(w,LAMBDA(b,TEXTJOIN("-",1,TEXTSPLIT(b,e,,1)))),mu,IFERROR(TEXTSPLIT(m,"-",,1)*1,0),md,IFERROR(TEXTAFTER(m,"-",-1,1,)*1,0),SUM(md,mu))Excel solution 12 for First and Last Number Sum, proposed by Asheesh Pahwa:
=SUM(
TOCOL(
MAP(
A2:A10,
LAMBDA(
x,
LET(
_t,
TEXTSPLIT(
x,
TEXTSPLIT(
x,
SEQUENCE(
10,
,
1
),
,
1
)
)+0,
tc,
TOCOL(
_t,
2
),
IF(
COUNTA(
tc
)=1,
tc,
SUM(
TAKE(
tc,
{1,
-1}
)
)
)
)
)
),
2
)
)Excel solution 13 for First and Last Number Sum, proposed by ferhat CK:
=SUM(
BYROW(
A2:A10,
LAMBDA(
x,
LET(
a,
REGEXEXTRACT(
x,
"[0-9]+",
1
),
n,
COUNTA(
a
),
IFNA(
IFS(
n>1,
SUM(
--TAKE(
a,
,
1
),
--TAKE(
a,
,
-1
)
),
n=1,
--TAKE(
a,
,
1
)
),
0
)
)
)
)
)Excel solution 14 for First and Last Number Sum, proposed by Jaroslaw Kujawa:
=SUM(
BYROW(
A2:A10;
LAMBDA(
x ;
LET(
y ;
--MID(
x ;
SEQUENCE(
;
LEN(
x
)
);
1
);
nums;
TEXTSPLIT(
TEXTJOIN(
"";
;
IFERROR(
XMATCH(
y;
SEQUENCE(
10;
;
0
);
0
)-1;
";"
)
);
";"
);
nums_f ;
FILTER(
nums;
nums<>""
);
SUM(
--IFERROR(
TAKE(
nums_f ;
;
IF(
COUNT(
--nums_f
)>1 ;
{1;
-1} ;
& 1
)
);
)
)
)
)
)
)Excel solution 15 for First and Last Number Sum, proposed by JvdV -:
=SUM(IFNA(--REGEXEXTRACT(A2:A10,{"d+","d.*DKd+"}),))Excel solution 16 for First and Last Number Sum, proposed by Gerson Pineda:
=SUM(MAP(A2:A10,LAMBDA(x,LET(n,--REGEXEXTRACT(x,"d+",1),IFNA(SUM((COUNT(n)>1)*@n,TAKE(n,,-1)),)))))Excel solution 17 for First and Last Number Sum, proposed by Ziad A.:
=SUMPRODUCT(IFNA(REGEXEXTRACT(A:A,"(d+)(?:.*D(d+))?")))Excel solution 18 for First and Last Number Sum, proposed by El Badlis Mohd Marzudin:
=SUM(MAP(A2:A10,LAMBDA(x,LET(a,REGEXEXTRACT(x,"d+",1)+0,IF(COUNT(a)>1,TAKE(a,,1)+TAKE(a,,-1),IFNA(a,))))))Solving the challenge of First and Last Number Sum with Python
Python solution 1 for First and Last Number Sum, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel(path, usecols = "A")
test = pd.read_excel(path, usecols = "B", nrows = 1).values[0][0]
result = input.copy()
result["Numbers"] = result["Strings"].str.findall(r"[-+]?d*.d+|d+").apply(lambda x: [int(i) for i in x])
result["Sum"] = result["Numbers"].apply(lambda x: x[0] + x[-1] if len(x) > 1 else x[0] if len(x) == 1 else 0)
result = result["Sum"].sum()
print(result == test) # True
Solving the challenge of First and Last Number Sum with Python in Excel
Python in Excel solution 1 for First and Last Number Sum, proposed by Alejandro Campos:
import re
data = xl("A2:A10")[0]
def process_string(s):
numbers = list(map(int, re.findall(r'd+', s)))
if len(numbers) == 0:
return 0
elif len(numbers) == 1:
return numbers[0]
else:
return numbers[0] + numbers[-1]
result = sum(map(process_string, data))
Python in Excel solution 2 for First and Last Number Sum, proposed by Abdallah Ally:
import re
# Create a function to extract numbers
def get_numbers(text):
nums = [int(x) for x in re.findall('d+', text)]
result = nums[0] + nums[-1] if len(nums) > 1 else sum(nums)
return result
df = xl("A1:A10", headers=True)
# Perform data manipulation
total = df['Strings'].map(get_numbers).sum()
total
Solving the challenge of First and Last Number Sum with R
R solution 1 for First and Last Number Sum, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B2")
result = input %>%
mutate(numbers = map(Strings, str_extract_all, "\d+")) %>%
unnest(numbers) %>%
unnest(numbers) %>%
mutate(rn = row_number(),
min = min(rn),
max = max(rn),
.by = Strings) %>%
filter(rn == min | rn == max) %>%
summarise(sum = sum(as.numeric(numbers)))
identical(result$sum, test$`Answer Expected`)
# [1] TRUE
&&