Today’s challenge is contributed by Sunny Baggu Extract the total numbers in the problem strings. For ex. 4597 To 4607 4609 To 4612 => 4597 to 4607 has 11 numbers (4597 and 4607 both inclusive) and 4609 to 4612 (4609 and 4612 both inclusive) has 4 numbers. Hence, total = 15
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 454
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Numbers from Ranges with Power Query
Power Query solution 1 for Count Numbers from Ranges, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Ans",
each List.Sum(
List.Transform(
Text.Split(Text.Replace(Text.Upper([Pronlem]), " TO ", "-"), " "),
each
let
n = List.Transform(Text.Split(_, "-"), Number.From)
in
List.Last(n) - n{0} + 1
)
)
)
in
AnsPower Query solution 2 for Count Numbers from Ranges, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
L = Text.Lower([Pronlem]),
R1 = Text.Replace(L, " to ", ".."),
R2 = Text.Replace(R1, " ", ", "),
G = Expression.Evaluate("{" & R2 & "}"),
R = List.Count(G)
][R]
)
in
ReturnPower Query solution 3 for Count Numbers from Ranges, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Accumulate({" To ", " to "}, [Pronlem], (s, c) => Text.Replace(s, c, "..")),
b = Text.Split(a, " "),
c = List.Sum(
List.Transform(
{0 .. List.Count(b) - 1},
each
if Text.Contains(b{_}, "..") then
List.Count(Expression.Evaluate("{" & b{_} & "}"))
else
1
)
)
in
c
)
in
SolPower Query solution 4 for Count Numbers from Ranges, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
sub = {{" ", ","}, {"to", ".."}, {",..,", ".."}},
acc = List.Count(
Expression.Evaluate(
"{"
& List.Accumulate(
{0 .. List.Count(sub) - 1},
Text.Lower([Pronlem]),
(s, c) => Text.Replace(s, sub{c}{0}, sub{c}{1})
)
& "}"
)
)
][acc]
)
in
resPower Query solution 5 for Count Numbers from Ranges, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = List.Transform,
a = T(S[Pronlem], Text.Lower),
b = T(a, each Text.Replace(_, " to ", "..")),
c = T(b, each Text.Split(_, " ")),
d = T(
c,
each T(
_,
each
if Text.Contains(_, "..") then
Expression.Evaluate(Text.Insert(Text.Insert(_, 0, "{"), Text.Length(_) + 1, "}"))
else
{_}
)
),
e = T(d, each List.Count(List.Combine(_))),
Sol = Table.FromColumns({e}, {"Answer Expected"})
in
SolPower Query solution 6 for Count Numbers from Ranges, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.TransformColumns(Source, {"Pronlem", each
let
TL = Text.Lower(_),
TR = Text.Replace (TL, " to ", ".."),
TS = Text.Split(TR, " "),
EE = List.Transform(TS, each
let
a = Text.Length(_),
b = Text.Insert(_,0,"{"),
c = Text.Insert(b, a + 1, "}"),
d = List.Count(Expression.Evaluate(c))
in
d
),
LS = List.Sum(EE)
in
LS
})
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 7 for Count Numbers from Ranges, proposed by Venkata Rajesh:
let
Source = Data,
Output = Table.AddColumn(
Source,
"Expected",
each [
l = Text.Lower([Problem]),
r = Text.Replace(l, " to ", ".."),
s = Text.Split(r, " "),
t = List.Sum(
List.Transform(
s,
each [
x = Text.Split(_, ".."),
y = List.Count({Number.From(x{0}) .. Number.From(List.Last(x))})
][y]
)
)
][t]
)
in
OutputSolving the challenge of Count Numbers from Ranges with Excel
Excel solution 1 for Count Numbers from Ranges, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
" To ",
" ",
,
1
),
SUM(
IFNA(
TAKE(
b,
,
-1
)-TAKE(
b,
,
1
),
)+1
)
)
)
)Excel solution 2 for Count Numbers from Ranges, proposed by Rick Rothstein:
=MAP(
A2:A9,
LAMBDA(
z,
REDUCE(
0,
TEXTSPLIT(
SUBSTITUTE(
UPPER(
z
),
" TO ",
"-"
),
" "
),
LAMBDA(
a,
x,
a+IF(
ISNUMBER(
FIND(
"-",
x
)
),
SUM(
TEXTSPLIT(
x,
"-"
)*{-1,
1}
)+1,
1
)
)
)
)
)Excel solution 3 for Count Numbers from Ranges, proposed by Rick Rothstein:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
t,
TEXTSPLIT(
SUBSTITUTE(
UPPER(
x
),
" TO ",
"-"
),
" "
),
SUM(
IFERROR(
TEXTAFTER(
t,
"-"
)-TEXTBEFORE(
t,
"-"
)+1,
1
)
)
)
)
)Excel solution 4 for Count Numbers from Ranges, proposed by John V.:
=MAP(
A2:A9,
LAMBDA(
x,
SUM(
1+IFNA(
BYROW(
TEXTSPLIT(
x,
" to ",
" ",
,
1
)*{-1,
1},
SUM
),
)
)
)
)Excel solution 5 for Count Numbers from Ranges, proposed by محمد حلمي:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
i,
TEXTSPLIT(
a,
" To ",
" ",
,
1
),
SUM(
IFNA(
TAKE(
i,
,
-1
)-TAKE(
i,
,
1
)+1,
1
)
)
)
)
)Excel solution 6 for Count Numbers from Ranges, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A9,
LAMBDA(
x,
SUM(
BYROW(
TEXTSPLIT(
x,
" to ",
" ",
,
1
),
LAMBDA(
r,
ROWS(
ISERR(
INDIRECT(
TEXTJOIN(
":",
,
r
)
)
)
)
)
)
)
)
)Excel solution 7 for Count Numbers from Ranges, proposed by Kris Jaganah:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
" to ",
" ",
1,
1
),
SUM(
IFNA(
TAKE(
a,
,
-1
)-TAKE(
a,
,
1
)+1,
1
)
)
)
)
)Excel solution 8 for Count Numbers from Ranges, proposed by Julian Poeltl:
=MAP(
A2:A9,
LAMBDA(
T,
LET(
SP,
TEXTSPLIT(
T,
{" to ",
" To "},
" "
),
SUM(
IFERROR(
TAKE(
SP,
,
-1
)-TAKE(
SP,
,
1
)+1,
1
)
)
)
)
)Excel solution 9 for Count Numbers from Ranges, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
l,
LOWER(
a
),
sub,
SUBSTITUTE(
l,
" to ",
"-"
),
splt,
TEXTSPLIT(
sub,
,
" "
),
r,
SUM(
TEXTAFTER(
splt,
"-",
,
,
,
0
) - TEXTBEFORE(
splt,
"-",
,
,
,
0
) + 1
),
r
)
)
)Excel solution 10 for Count Numbers from Ranges, proposed by Timothée BLIOT:
=MAP(
A2:A9,
LAMBDA(
z,
LET(
N,
TOCOL(
--REGEXEXTRACT(
z,
"d+",
1
)
),
M,
SUM(
REGEXEXTRACT(
z,
"(?<=To )d+",
1,
1
)-1---REGEXEXTRACT(
z,
"d+(?= To)",
1,
1
)
),
IFNA(
M,
0
)+IFNA(
ROWS(
N
),
0
)
)
)
)Excel solution 11 for Count Numbers from Ranges, proposed by Hussein SATOUR:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
SUBSTITUTE(
LOWER(
x
),
" to ",
"/"
),
,
" "
),
SUM(
IFNA(
TEXTAFTER(
a,
"/"
)-TEXTBEFORE(
a,
"/"
),
0
)+1
)
)
)
)Excel solution 12 for Count Numbers from Ranges, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
t,
-TEXTSPLIT(
a,
" To ",
" ",
,
1,
),
SUM(
BYROW(
t,
LAMBDA(
r,
1+MAX(
,
SUM(
r*{1,
-1}
)
)
)
)
)
)
)
)Excel solution 13 for Count Numbers from Ranges, proposed by Duy Tùng:
=MAP(
A2:A9,
LAMBDA(
v,
SUM(
IFNA(
BYROW(
TEXTSPLIT(
v,
" to ",
" ",
,
1
)*{-1,
1},
SUM
)+1,
1
)
)
)
)Excel solution 14 for Count Numbers from Ranges, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
_a,
TEXTSPLIT(
SUBSTITUTE(
SUBSTITUTE(
x,
" To ",
"#"
& ),
" to ",
"#"
),
,
" "
),
SUM(
IFERROR(
TEXTAFTER(
_a,
"#"
) - TEXTBEFORE(
_a,
"#"
) + 1,
1
)
)
)
)
)Excel solution 15 for Count Numbers from Ranges, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A9,
LAMBDA(
p,
LET(
d,
TEXTSPLIT(
p,
{" to ";" To "},
" "
),
SUM(
IFNA(
TAKE(
d,
,
-1
)-TAKE(
d,
,
1
)+1,
1
)
)
)
)
)Excel solution 16 for Count Numbers from Ranges, proposed by Abdallah Ally:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
x,
b,
TEXTSPLIT(
SUBSTITUTE(
LOWER(
a
),
" to ",
"-"
),
" "
),
REDUCE(
0,
b,
LAMBDA(
x,
y,
LET(
c,
TEXTSPLIT(
y,
"-"
),
x+1+TAKE(
c,
,
-1
)-TAKE(
c,
,
1
)
)
)
)
)
)
)Excel solution 17 for Count Numbers from Ranges, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A9,
LAMBDA(
r,
LET(
m,
{-1,
1}*TEXTSPLIT(
r,
" To ",
" ",
,
1
),
SUM(
IFNA(
m,
-INDEX(
m,
,
1
)
)+{0,
1}
)
)
)
)Excel solution 18 for Count Numbers from Ranges, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A9,
v,
MAP(
z,
LAMBDA(
x,
SUBSTITUTE(
SUBSTITUTE(
TRIM(
x
),
" to ",
"-"
),
" To ",
"-"
)
)
),
w,
MAP(
v,
LAMBDA(
y,
LET(
a,
TEXTSPLIT(
y,
,
" "
),
b,
MAP(
a,
LAMBDA(
x,
COUNT(
SEQUENCE(
SUM(
TEXTSPLIT(
x,
"-"
)*{-1,
1}
)+1,
,
ABS(
TEXTBEFORE(
x,
"-"
)
)
)
)
)
),
d,
SUM(
IF(
b=0,
1,
b
)
),
d
)
)
),
w
)Excel solution 19 for Count Numbers from Ranges, proposed by Andy Heybruch:
=MAP(
LOWER(
A2:A9
),
LAMBDA(
_rng,
LET(
_array,
TEXTSPLIT(
_rng,
" to ",
" "
),
SUM(
IFERROR(
TAKE(
_array,
,
-1
)-TAKE(
_array,
,
1
)+1,
1
)
)
)
)
)Excel solution 20 for Count Numbers from Ranges, proposed by Andy Heybruch:
=MAP(
A2:A9,
LAMBDA(
_rng,
REDUCE(
0,
TEXTSPLIT(
SUBSTITUTE(
UPPER(
_rng
),
" TO ",
"-"
),
,
" "
),
LAMBDA(
a,
v,
a+IFERROR(
IF(
SEARCH(
"-",
v
)>0,
--TEXTAFTER(
v,
"-"
)-TEXTBEFORE(
v,
"-"
)+1
),
1
)
)
)
)
)Excel solution 21 for Count Numbers from Ranges, proposed by JvdV -:
=MAP(
SUBSTITUTE(
LOWER(
A2:A9
),
" to ",
":A"
),
LAMBDA(
s,
SUM(
MAP(
INDIRECT(
"A"&TEXTSPLIT(
s,
" "
)
),
ROWS
)
)
)
)Excel solution 22 for Count Numbers from Ranges, proposed by Pieter de Bruijn:
=MAP(
A2:A9,
LAMBDA(
a,
SUM(
BYROW(
TEXTSPLIT(
LOWER(
a
),
" to ",
" "
)*{-1,
1},
LAMBDA(
x,
IFERROR(
SUM(
x
)+1,
1
)
)
)
)
)
)Excel solution 23 for Count Numbers from Ranges, proposed by Sandeep Marwal:
=MAP(
SUBSTITUTE(
LOWER(
A2:A9
),
" to ",
"|"
),
LAMBDA(
x,
SUM(
IF(
ISNUMBER(
--TEXTSPLIT(
x,
" "
)
),
1,
TEXTAFTER(
TEXTSPLIT(
x,
" "
),
"|"
)-TEXTBEFORE(
TEXTSPLIT(
x,
" "
),
"|"
)+1
)
)
)
)Excel solution 24 for Count Numbers from Ranges, proposed by Tyler Cameron:
=MAP(
A2:A9,
LAMBDA(
t,
LET(
a,
TEXTSPLIT(
SUBSTITUTE(
t,
"t",
"T"
),
" To ",
" "
),
SUM(
IFERROR(
IFNA(
CHOOSECOLS(
a,
2
)-TAKE(
a,
,
1
)+1,
1
),
1
)
)
)
)
)Excel solution 25 for Count Numbers from Ranges, proposed by Will Freestone:
=BYROW(
A2:A9,
LAMBDA(
r,
LET(
t,
TEXTSPLIT(
SUBSTITUTE(
LOWER(
r
),
" to ",
":"
),
" "
),
SUM(
IFERROR(
BYCOL(
t,
LAMBDA(
c,
ROWS(
@INDIRECT(
c
)
)
)
),
1
)
)
)
)
)Excel solution 26 for Count Numbers from Ranges, proposed by Victor Betancurt:
=MAP(
A2:A9,
LAMBDA(
x,
SUM(
LET(
a,
SUBSTITUTE(
LOWER(
x
),
" to ",
"_to_"
),
b,
TEXTSPLIT(
a,
" "
),
c,
FIND(
"_",
b,
1
),
d,
LEN(
b
),
e,
IFERROR(
c ^ 0,
0
) = 1,
IF(
e,
MID(
b,
c + 4,
d
) * 1 - LEFT(
b,
c - 1
) * 1 + 1,
1
)
)
)
)
)Solving the challenge of Count Numbers from Ranges with Python
Python solution 1 for Count Numbers from Ranges, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel("454 Extraction of number of nodes.xlsx", usecols="A")
test = pd.read_excel("454 Extraction of number of nodes.xlsx", usecols="B")
def replace_notation_with_range(text_vector):
def replace_match(match):
numbers = list(map(int, match.group().split(" to ")))
range_values = list(range(numbers[0], numbers[1]+1))
return ", ".join(map(str, range_values))
return [re.sub("\d+ to \d+", replace_match, text) for text in text_vector]
def count_numbers(text_vector):
return [len(re.findall("\d+", text)) for text in text_vector]
result = input.copy()
result["Pronlem"] = result["Pronlem"].str.lower()
result["Pronlem"] = replace_notation_with_range(result["Pronlem"])
result["Count"] = count_numbers(result["Pronlem"])
result = result[["Count"]]
print(result["Count"].equals(test["Answer Expected"])) # True
Solving the challenge of Count Numbers from Ranges with Python in Excel
Python in Excel solution 1 for Count Numbers from Ranges, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_454 - Extraction of number of nodes.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
def count_extracted_numbers(col):
numbers = 0
s = col.lower().replace(' to ', '-').split(' ')
for a in s:
if a.find('-') == -1: numbers += 1
else: numbers += 1- eval(a)
return numbers
df['My Answer'] = df['Pronlem'].apply(count_extracted_numbers)
df
Solving the challenge of Count Numbers from Ranges with R
R solution 1 for Count Numbers from Ranges, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/454 Extraction of number of nodes.xlsx", range = "A1:A9")
test = read_excel("Excel/454 Extraction of number of nodes.xlsx", range = "B1:B9")
replace_notation_with_range <- function(text_vector) {
str_replace_all(text_vector, "\d+ to \d+", function(match) {
numbers <- str_split(match, " to ") %>%
unlist() %>%
as.numeric()
range <- seq(from = numbers[1], to = numbers[2])
paste(range, collapse = ", ")
})
}
count_numbers <- function(text_vector) {
str_count(text_vector, "\d+") %>%
as.numeric()
}
result = input %>%
mutate(Pronlem = str_to_lower(Pronlem)) %>%
mutate(Pronlem = map_chr(Pronlem, replace_notation_with_range)) %>%
mutate(Count = count_numbers(Pronlem)) %>%
select(Count)
identical(result$Count, test$`Answer Expected`)
# [1] TRUE
&&