Sum all the numbers given in column A except the first and last numbers in every cell.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 688
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sum Without First And Last with Power Query
Power Query solution 2 for Sum Without First And Last, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Text = Text.ToList(Text.Remove(Text.Combine(Source[Strings]), {"0" .. "9"})),
Sol = List.Sum(
List.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = List.Accumulate(Text, [Strings], (s, c) => Text.Replace(s, c, " ")),
b = List.Select(Text.Split(a, " "), each _ <> ""),
c = List.Transform(List.RemoveLastN(List.RemoveFirstN(b)), Number.From)
in
c
)[A]
)
)
in
SolPower Query solution 3 for Sum Without First And Last, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToRows(S),
Fx = (x) =>
let
b = List.Transform(x, Text.ToList){0},
c = List.Transform(b, each try Number.From(_) otherwise null),
d = Table.Group(
Table.FromColumns({c}),
"Column1",
{"G", each Text.Combine(List.Transform([Column1], Text.From))},
0,
(x, y) => Number.From(y = null)
)[G],
e = List.RemoveNulls(List.Transform(d, Number.From)),
f = List.Sum(List.Skip(List.RemoveLastN(e)))
in
f,
g = List.RemoveNulls(List.Transform(a, each Fx(_))),
Sol = Table.FromValue(List.Sum(g), [DefaultColumnName = "Answer Expected"])
in
SolPower Query solution 4 for Sum Without First And Last, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromValue(
List.Sum(
List.TransformMany(
Source[Strings],
(f) =>
List.RemoveLastN(
List.Skip(
List.Select(
Text.SplitAny(f, Text.Combine(Text.SplitAny(f, "0123456789"))),
each _ > ""
)
),
1
),
(x, y) => Number.From(y)
)
),
[DefaultColumnName = "Result"]
)
in
ResultPower Query solution 5 for Sum Without First And Last, proposed by Antriksh Sharma:
let
Source = Table,
Sum = List.Sum(
List.TransformMany(
Source[Strings],
(x) => {
List.Transform(
Text.SplitAny(x, Text.Combine(Text.SplitAny(x, "0123456789"))),
Number.FromText
)
},
(x, y) => List.Sum(List.RemoveLastN(List.Skip(List.RemoveNulls(y)), 1))
)
)
in
SumPower Query solution 6 for Sum Without First And Last, proposed by Antriksh Sharma:
let
Source = Table,
Transform = List.Transform(
Source[Strings],
(x) =>
let
a = Text.ToList(x),
b = List.Accumulate(
a,
"",
(s, c) => {s & "(-_-)", s & c}{Byte.From(Value.FromText(c) is number)}
),
c = List.Select(Text.Split(b, "(-_-)"), each _ <> ""),
d = List.Transform(List.RemoveLastN(List.Skip(c), 1), Number.FromText)
in
d
),
Sum = List.Sum(List.Combine(Transform))
in
SumPower Query solution 7 for Sum Without First And Last, proposed by Peter Krkos:
PowerQuery solution:
List.Sum(List.Transform(List.Combine(Table.AddColumn(Source, "L", each
List.RemoveLastN(List.Skip(List.RemoveMatchingItems(Splitter.SplitTextByEachDelimiter(Text.ToList(Text.Remove([Strings], {"0".."9"})))([Strings]), {""})), 1))[L]), Number.From))
}})
Power Query solution 8 for Sum Without First And Last, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
LstSum = List.Sum(
Table.AddColumn(
source,
"Helper",
each List.Sum(
List.Transform(
List.RemoveLastN(
List.RemoveFirstN(
List.Select(
Text.Split(
Text.Trim(
List.Accumulate(
Text.ToList([Strings]),
"",
(accum, curr) =>
if List.Contains({"0" .. "9"}, curr) then accum & curr else accum & " "
)
),
" "
),
each Text.Length(_) > 0
),
1
),
1
),
each Number.FromText(_)
)
)
)[Helper]
)
in
LstSumPower Query solution 9 for Sum Without First And Last, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lst = Table.TransformColumns(
Source,
{
"Strings",
each List.Combine(
{
List.RemoveLastN(
List.Skip(
List.RemoveNulls(
List.Transform(
Text.SplitAny(_, Text.Combine(Text.SplitAny(_, "0123456789"))),
Number.FromText
)
)
),
1
)
}
)
}
),
Res = List.Sum(Table.ExpandListColumn(Lst, "Strings")[Strings])
in
ResSolving the challenge of Sum Without First And Last with Excel
Excel solution 1 for Sum Without First And Last, proposed by Bo Rydobon 🇹🇭:
=SUM(
--REGEXEXTRACT(
CONCAT(
REGEXREPLACE(
A2:A10,
"^D*d+|d+D*$",
)
),
"d+",
1
)
)Excel solution 2 for Sum Without First And Last, proposed by Rick Rothstein:
=SUM(IFERROR(MAP(A2:A10,LAMBDA(x,LET(m,MID(x,SEQUENCE(99),1),SUM(0+DROP(DROP(TEXTSPLIT(TRIM(CONCAT(IFERROR(0+m," "))),," "),1),-1))))),))Excel solution 3 for Sum Without First And Last, proposed by Kris Jaganah:
=SUM(TOCOL(MAP(A2:A10,LAMBDA(x,SUM(--DROP(DROP(REGEXEXTRACT(x,"[0-9]+",1),,1),,-1)))),3))Excel solution 4 for Sum Without First And Last, proposed by Aditya Kumar Darak 🇮🇳:
=SUM(
MAP(
A2:A10,
LAMBDA(
a,
IFERROR(
SUM(
--DROP(
DROP(
REGEXEXTRACT(
a,
"d+",
1
),
,
1
),
,
-1
)
),
0
)
)
)
)Excel solution 5 for Sum Without First And Last, proposed by Timothée BLIOT:
=SUM(--(REGEXEXTRACT(
CONCAT(
REGEXREPLACE(
A2:A10,
"(?<=^D{0,99})d+|d+(?=D*$)",
""
)
),
"d+",
1
)))Excel solution 6 for Sum Without First And Last, proposed by Hussein SATOUR:
=SUM(MAP(A2:A10,LAMBDA(x,SUM(IFERROR(DROP(DROP(--REGEXEXTRACT(x,"d+",1),,1),,-1),0)))))Excel solution 7 for Sum Without First And Last, proposed by Duy Tùng:
=SUM(
IFERROR(
MAP(
A2:A10,
LAMBDA(
v,
SUM(
DROP(
DROP(
--REGEXEXTRACT(
v,
"d+",
1
),
,
1
),
,
-1
)
)
)
),
""
)
)Excel solution 8 for Sum Without First And Last, proposed by Sunny Baggu:
=SUM(
TOCOL(
MAP(
A2:A10,
LAMBDA(a,
SUM(
DROP(
DROP(
TEXTSPLIT(a, , TEXTSPLIT(a, SEQUENCE(10, , 0), , 1), 1) + 0,
1
),
-1
)
)
)
),
3
)
)Excel solution 9 for Sum Without First And Last, proposed by LEONARD OCHEA 🇷🇴:
=LET(
D,
DROP,
S,
SUM,
S(
MAP(
A2:A10,
LAMBDA(
x,
IFERROR(
S(
--D(
D(
REGEXEXTRACT(
x,
"d+",
1
),
,
1
),
,
-1
)
),
)
)
)
)
)
=REDUCE(
0,
A2:A10,
LAMBDA(
a,
x,
a+IFERROR(
SUM(
--DROP(
DROP(
REGEXEXTRACT(
x,
"d+",
1
),
,
1
),
,
-1
)
),
)
)
)Excel solution 10 for Sum Without First And Last, proposed by Md. Zohurul Islam:
=REDUCE(0,A2:A10,LAMBDA(x,y,x+SUM(IFERROR(ABS(DROP(DROP(REGEXEXTRACT(y, "d+", 1),,1),,-1)),0))))Excel solution 11 for Sum Without First And Last, proposed by Md. Zohurul Islam:
=LET(u,CHAR(SEQUENCE(255)),v,FILTER(u,ISERROR(ABS(u))),
REDUCE(0,A2:A10,LAMBDA(x,y,x+SUM(IFERROR(DROP(DROP(--TEXTSPLIT(y,v,,1),,1),,-1),0))
)))Excel solution 12 for Sum Without First And Last, proposed by Hamidi Hamid:
=LET(x,MAP(A2:A10,LAMBDA(a,IFERROR(TEXTJOIN(" ",TRUE,FILTERXML(""®EXREPLACE(a,"[^d]"," ")&"Excel solution 13 for Sum Without First And Last, proposed by Dhaval Patel:
=SUM(
BYROW(
A2:A10,
LAMBDA(
txt,
LET(
l,
LEN(
txt
),
s,
SEQUENCE(
l
),
c,
MID(
txt,
s,
1
),
r,
IF(
ISNUMBER(
--c
),
c,
" "
),
cleaned,
TRIM(
CONCAT(
r
)
),
parts,
TEXTSPLIT(
cleaned,
" "
),
cnt,
COUNTA(
parts
),
midParts,
IF(
cnt>2,
INDEX(
parts,
SEQUENCE(
cnt-2,
,
2
)
),
""
),
result,
IF(
cnt>2,
SUM(
--midParts
),
0
),
result
)
)
)
)Excel solution 14 for Sum Without First And Last, proposed by ferhat CK:
=REDUCE(0,A2:A10,LAMBDA(x,y,x+SUM(IFERROR(DROP(DROP(--REGEXEXTRACT(y,"d+",1),,1),,-1),0))))Excel solution 15 for Sum Without First And Last, proposed by Jaroslaw Kujawa:
=SUM(MAP(A2:A10;LAMBDA(y;LET(ys;TEXTSPLIT(REDUCE("";MID(y;SEQUENCE(LEN(y));1);LAMBDA(a;x;IF(ISNUMBER(1*RIGHT(a))*ISNUMBER(1*x);a&x;a&"|"&x)));;"|");SUM(IFERROR(--DROP(DROP(FILTER(ys;ISNUMBER(--ys));1);-1);))))))Excel solution 16 for Sum Without First And Last, proposed by Meganathan Elumalai:
=SUM(
MAP(
A2:A10,
LAMBDA(
x,
IFERROR(
SUM(
DROP(
DROP(
--TEXTSPLIT(
x,
TEXTSPLIT(
x,
SEQUENCE(
,
10,
0
),
,
1
),
,
1
),
,
1
),
,
-1
)
),
0
)
)
)
)Excel solution 17 for Sum Without First And Last, proposed by CA Raghunath Gundi:
=SUM(
BYROW(
A2:A10,
LAMBDA(
a,
LET(
sp,
--TEXTSPLIT(
TRIM(
CONCAT(
IFERROR(
0+MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
" "
)
)
),
,
" "
),
ans,
SUM(
--DROP(
DROP(
sp,
1
),
-1
)
),
IFERROR(
ans,
0
)
)
)
)
)Excel solution 18 for Sum Without First And Last, proposed by Eddy Wijaya:
=SUM(
BYROW(
A2:A10,
LAMBDA(
r,
LET(
c,
CHAR(
SEQUENCE(
255
)
),
l,
FILTER(
c,
ISERROR(
--c
)
),
IFERROR(
SUM(
--DROP(
DROP(
TEXTSPLIT(
r,
l,
,
TRUE
),
,
1
),
,
-1
)
),
0
)
)
)
)
)Excel solution 19 for Sum Without First And Last, proposed by Ziad A.:
=SUMPRODUCT(SPLIT(REGEXREPLACE(A2:A10,"^D*d+|d+D*$|D"," ")," "))Excel solution 20 for Sum Without First And Last, proposed by Maciej Kopczyński:
=SUM(
--TEXTSPLIT(
TEXTJOIN(" ", TRUE, TOCOL(
BYROW(A2:A10, LAMBDA(row,
TEXTJOIN(" ", TRUE, DROP(DROP(REGEXEXTRACT(row, "d+", 1), , -1), , 1))
)),
3
)),
" "
)
)Excel solution 21 for Sum Without First And Last, proposed by Erdit Qendro:
=SUM(BYROW(A2:A10,
LAMBDA(a,
SUM(IFERROR(
DROP(DROP(REGEXEXTRACT(a,"d+",1)+0,,1),,-1),0)))))Excel solution 22 for Sum Without First And Last, proposed by Fredson Alves Pinho:
=REDUCE(0,
A2:A10,
LAMBDA(a,
v,
LET(r,
REGEXEXTRACT(
v,
"d+",
1
),
n,
COLUMNS(
r
),
IFNA(SUM((ABS(
SEQUENCE(
,
n,
n-1,
-2
)
)Excel solution 23 for Sum Without First And Last, proposed by Ernesto Vega Castillo:
=REDUCE(
0,
A2:A10,
LAMBDA(
x,
y,
x+IFERROR(
SWITCH(
TRUE,
REGEXTEST(
y,
"d+"
),
SUM(
--DROP(
DROP(
REGEXEXTRACT(
y,
"d+",
1
),
,
1
),
,
-1
),
0
)
),
0
)
)
)Excel solution 24 for Sum Without First And Last, proposed by red craven:
=SUM(IFERROR(MAP(A2:A10,LAMBDA(x,SUM(--DROP(DROP(REGEXEXTRACT(x,"d+",1),,1),,-1)))),0))Excel solution 25 for Sum Without First And Last, proposed by CA Mohit Saxena:
=SUM(BYROW(A2:A10,LAMBDA(r,LET(m,MID(r,SEQUENCE(,LEN(r)),1),SUM(DROP(DROP(TOROW(--MAP(m,LAMBDA(s,IFERROR(SUM(0+s),""))),3),,1),,-1))))))Solving the challenge of Sum Without First And Last with Python
Python solution 1 for Sum Without First And Last, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=1).squeeze().tolist()
input['cell_sum'] = input['Strings'].apply(lambda x: sum(int(num) for num in re.findall(r'd+', str(x))[1:-1]))
result = input['cell_sum'].sum()
print(result == test) # True
Python solution 2 for Sum Without First And Last, proposed by Claudiu B.:
import pandas as pd
df = pd.read_excel(r"string_to_numbers.xlsx")
df['temp'] = df['Strings'].str.findall( '(?:(?<=D)|^)(d+)(?=D|$)' ).str[1:-1]
lst = list(df['temp'])
numbers = [ int(i) for l in lst for i in l ]
total_sum = sum(numbers)
print(total_sum)
Solving the challenge of Sum Without First And Last with Python in Excel
Python in Excel solution 1 for Sum Without First And Last, proposed by Alejandro Campos:
import re
column_A = xl("A2:A10")[0]
total_sum = 0
for cell in column_A:
numbers = list(map(int, re.findall(r'd+', cell)))
if len(numbers) > 2:
total_sum += sum(numbers[1:-1])
'Total Sum', total_sum
Python in Excel solution 2 for Sum Without First And Last, proposed by Aditya Kumar Darak 🇮🇳:
import re
df = xl("A1:A10", True)
result = sum(
sum(n[1:-1]) if len(n) > 2 else 0
for n in [list(map(int, re.findall(r"d+", s))) for s in df["Strings"]]
)
result
Python in Excel solution 3 for Sum Without First And Last, proposed by Antriksh Sharma:
df = xl("A1:A10", headers= True)
def sum_nums(word):
a = [char if char.isdigit() else '(-_-)' for char in word]
b = ''.join(a).split('(-_-)')
c = list(filter(None, b))
d = sum(map(int, c[1:-1]))
return d
df['x'] = df['Strings'].apply(sum_nums)
df['x'].sum()
Solving the challenge of Sum Without First And Last with R
R solution 1 for Sum Without First And Last, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B2") %>%
pull()
result = input %>%
mutate(numbers = str_extract_all(Strings, "\d+"),
numbers = map(numbers, ~ as.numeric(.x)),
sum = map_dbl(numbers, ~ sum(.x[-c(1, length(.x))]))) %>%
summarise(sum = sum(sum)) %>%
pull()
test == result #> True
&&