Take ith and (i+1)th digits, multiply them and insert the result between the same digits. Ex. 2905 2*9 = 18 => 2189 9*0 = 0 => 218900 0*5 = 0 => 21890005
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 424
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Insert Product Between Digits with Power Query
_x000D_Power Query solution 1 for Insert Product Between Digits, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
A = Text.At, T = Text.From, N = Number.From,
R = Table.AddColumn(S, "R", each
let
a = T([Words]),
b = List.Accumulate({1..Text.Length(a) - 1}, A(a, 0), (s, c) => s & T(N(Text.End(s, 1)) * N(A(a, c))) & A(a, c))
in
b
)[[R]]
in
R
Blessings!
Power Query solution 2 for Insert Product Between Digits, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Words]),
L = Text.Length(T),
S = Text.ToList(T),
N = List.Transform(S, Number.From),
G = List.Generate(
() => [a = 0, c = S{a}],
each [a] < L,
each [a = [a] + 1, b = Text.From(N{a} * N{[a]}), c = [c] & b & S{a}],
each [c]
),
R = List.Last(G)
][R]
)
in
ReturnPower Query solution 3 for Insert Product Between Digits, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Words]),
S = Text.ToList(T),
N = List.Transform(S, Number.From),
Z = List.Skip(List.Zip({S, N})),
R = List.Accumulate(
Z,
[a = S{0}, b = N{0}],
(x, y) => [a = x[a] & Text.From(y{1} * x[b]) & y{0}, b = y{1}]
)[a]
][R]
)
in
ReturnPower Query solution 4 for Insert Product Between Digits, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumnTypes(S, {{"Words", type text}}),
Fx = (x) =>
let
n = x,
L = List.Transform,
a = L(Text.ToList(n), Number.From),
b = List.Generate(
() => [i = 0, j = 1],
each [i] < List.Count(a),
each [i = [i] + 1, j = [j] + 1],
each List.Range(a, [i], 2)
),
c = List.RemoveLastN(b),
d = L(c, each {_{0} * _{1}}),
e = L({0 .. List.Count(d) - 1}, each c{_} & d{_}),
f = L(e, each L(_, Text.From)),
g = L(f, each _{0} & _{2} & _{1}),
h = List.Generate(
() => [i = 0, j = g{0}],
each [i] < List.Count(g),
each [i = [i] + 1, j = Text.RemoveRange([j], Text.Length([j]) - 1) & g{[i] + 1}],
each [j]
),
i = List.Last(h)
in
i,
Sol = Table.AddColumn(T, "Answer Expected", each Fx([Words]))
in
SolPower Query solution 5 for Insert Product Between Digits, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content], T = Text.From, TL = Text.ToList,
Result = Table.TransformColumns(Source, {"Words", each
let
TF = T(_),
TTL = List.Transform(TL(TF), each Number.From(_)),
LC = {0..List.Count(TTL)-2},
LA = List.Accumulate(LC, {},
(s,c) =>
let
TR = List.Range(TTL, c, 2),
Mx = T(TR{0} * TR{1}),
TTL2 = TL(Mx),
LIR = List.InsertRange(TR,1,TTL2),
TT = List.Transform(LIR, each T(_)),
LR = List.RemoveLastN(TT)
in
s & LR
) & {Text.End(TF,1)}
in
Text.Combine(LA)
})
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 6 for Insert Product Between Digits, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "tSourceXl424"]}[Content],
AddList = Table.AddColumn(
Source,
"Answer expected",
each
let
a = Text.Length(Text.From([Words])) - 1,
w = Text.From([Words]),
l = Text.ToList(Text.From([Words]))
in
List.Last(
List.Generate(
() => [
Counter = 0,
x = l{Counter},
u = l{Counter + 1},
s = x & Text.From(Number.From(x) * Number.From(u)) & u
],
each [Counter] < a,
each [
Counter = [Counter] + 1,
x = l{Counter},
u = l{Counter + 1},
s = Text.Range([s], 0, Text.Length([s]) - 1)
& x
& Text.From(Number.From(x) * Number.From(u))
& u
],
each [s]
)
)
)
in
AddListSolving the challenge of Insert Product Between Digits with Excel
_x000D_Excel solution 1 for Insert Product Between Digits, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
TEXTJOIN(
IFNA(
m*DROP(
m,
1
),
),
,
m
)
)
)
)
=MAP(
A2:A10,
LAMBDA(
b,
REDUCE(
,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
LAMBDA(
a,
v,
a&RIGHT(
a
)*v&v
)
)
)
)Excel solution 2 for Insert Product Between Digits, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)
),
m,
MID(
x,
s,
1
),
CONCAT(
TOCOL(
HSTACK(
m,
m*MID(
x,
s+1,
1
)
),
2
)
)
)
)
)Excel solution 3 for Insert Product Between Digits, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
r,
REDUCE(
r,
SEQUENCE(
LEN(
r
)-1,
,
LEN(
r
),
-1
),
LAMBDA(
a,
x,
REPLACE(
a,
x,
0,
MID(
r,
x,
1
)*MID(
r,
x-1,
1
)
)
)
)
)
)Excel solution 4 for Insert Product Between Digits, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)-1
),
a,
MID(
x,
s,
1
),
b,
MID(
x,
1+s,
1
),
CONCAT(
@a,
a*b&b
)
)
)
)Excel solution 5 for Insert Product Between Digits, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
i,
LET(
s,
SEQUENCE(
LEN(
i
)-1
),
r,
MID(
i,
s,
1
),
x,
MID(
i,
1+s,
1
),
TAKE(
SCAN(
@r&@r*@x&@x,
s,
LAMBDA(
a,
d,
IFERROR(
CONCAT(
a,
INDEX(
r*x,
d+1
),
INDEX(
x,
d+1
)
),
a
)
)
),
-1
)
)
)
)Excel solution 6 for Insert Product Between Digits, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
e,
CONCAT(
MAP(
SEQUENCE(
LEN(
e
)-1
),
LAMBDA(
x,
MID(
e,
x,
1
)&--MID(
e,
x,
1
)*--MID(
e,
x+1,
1
)
)
)
)&RIGHT(
e
)
)
)Excel solution 7 for Insert Product Between Digits, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
l,
LEN(
a
),
d,
MID(
a,
SEQUENCE(
LEN(
a
),
,
,
1
),
1
),
e,
TAKE(
d,
l-1
),
f,
TAKE(
d,
1-l
),
g,
e*f,
h,
""&g&f,
TAKE(
d,
1
)&TEXTJOIN(
"",
,
h
)
)
)
)Excel solution 8 for Insert Product Between Digits, proposed by Duy Tùng:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
a,
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
CONCAT(
TOCOL(
HSTACK(
a,
DROP(
a,
-1
)*DROP(
a,
1
)
),
3
)
)
)
)
)Excel solution 9 for Insert Product Between Digits, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
_s,
SEQUENCE(
LEN(
x
)
),
_a,
MID(
x,
DROP(
_s,
-1
),
1
),
_c,
MID(
x,
DROP(
_s,
1
),
1
),
_d,
_a & _a * _c & _c,
UNIQUE(
IFERROR(
TAKE(
_d,
1
) & CONCAT(
MID(
DROP(
_d,
1
),
2,
10
)
),
_d
)
)
)
)
)Excel solution 10 for Insert Product Between Digits, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)-1
),
i,
MID(
a,
s,
1
),
j,
MID(
a,
s+1,
1
),
@i&CONCAT(
i*j&j
)
)
)
)
With recursive function
=LET(
F,
LAMBDA(
F,
w,
x,
y,
LET(
a,
MID(
w,
x+1,
1
),
b,
MID(
w,
x,
1
),
z,
y&b*a&a,
IF(
x+1Excel solution 11 for Insert Product Between Digits, proposed by Andy Heybruch:
=MAP(
A2:A10,
LAMBDA(
_a,
REDUCE(
"",
--MID(
_a,
SEQUENCE(
,
LEN(
_a
)
),
1
),
LAMBDA(
a,
v,
IF(
a="",
v,
& a&v*RIGHT(
a
)&v
)
)
)
)
)Excel solution 12 for Insert Product Between Digits, proposed by Edwin Tisnado:
=MAP(
A2:A10,
LAMBDA(
v,
LET(
m,
--MID(
v,
SEQUENCE(
LEN(
v
)
),
1
),
n,
DROP(
m,
1
),
p,
DROP(
m*n,
-1
),
CONCAT(
IFNA(
HSTACK(
m,
p
),
""
)
)
)
)
)Excel solution 13 for Insert Product Between Digits, proposed by Songglod P.:
=LET(
nums,
MID(
A2,
SEQUENCE(
1,
LEN(
A2
)
),
1
),
idx,
SEQUENCE(
1,
COLUMNS(
nums
)-1,
2
),
muls,
MAP(
nums,
idx,
LAMBDA(
x,
i,
x*INDEX(
nums,
1,
i
)
)
),
results,
FILTER(
muls,
NOT(
ISNA(
muls
)
)
),
REDUCE(
TEXTJOIN(
"/",
TRUE,
nums
),
results,
LAMBDA(
curr,
val,
SUBSTITUTE(
curr,
"/",
val,
1
)
)
)
)Excel solution 14 for Insert Product Between Digits, proposed by Ernesto Vega Castillo:
=BYROW(
A2:A10,
LAMBDA(
w,
REDUCE(
"",
IFERROR(
LET(
a,
MID(
w,
SEQUENCE(
LEN(
w
)
),
1
),
b,
MID(
w,
1+SEQUENCE(
LEN(
w
)
),
1
),
a&a*b&TAKE(
LEFT(
b
),
-1
)
),
""
),
LAMBDA(
m,
z,
m&z
)
)&RIGHT(
w,
1
)
)
)Excel solution 15 for Insert Product Between Digits, proposed by Tyler Cameron:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
a,
--MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
CONCAT(
TOCOL(
HSTACK(
a,
MAKEARRAY(
LEN(
t
),
1,
LAMBDA(
r,
c,
INDEX(
a,
r
)*INDEX(
a,
r+1
)
)
)
),
3
)
)
)
)
)Solving the challenge of Insert Product Between Digits with Python in Excel
_x000D_Python in Excel solution 1 for Insert Product Between Digits, proposed by John V.:
Hi everyone!
One [Py] could be:
Blessings!
Solving the challenge of Insert Product Between Digits with R
_x000D_R solution 1 for Insert Product Between Digits, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/424 Insert In Between Multiplication.xlsx", range = "A1:A10")
test = read_excel("Excel/424 Insert In Between Multiplication.xlsx", range = "B1:B10")
transform_number = function(number){
str_number = as.character(number)
digits = strsplit(str_number, "")[[1]] %>% as.numeric()
ndigits = length(digits)
products = map(1:(ndigits-1), ~{
digits[.x] * digits[.x+1]
}) %>% c(., "")
result = map2(digits,products, ~{
c(.x, .y)
}) %>% unlist() %>%
paste(collapse = "")
return(result)
}
result = input %>%
mutate(`Answer Expected` = map_chr(Words, transform_number)) %>%
select(-Words)
Solving the challenge of Insert Product Between Digits with Excel VBA
_x000D_Excel VBA solution 1 for Insert Product Between Digits, proposed by Nicolas Micot:
VBA code:
Function f_challenge424(ByVal texte As String) As String
Dim numCar As Integer, chiffre1 As Integer, chiffre2 As Integer
Dim resultat As String
For numCar = 1 To Len(texte)
If numCar > 1 Then
chiffre2 = Mid(texte, numCar, 1)
resultat = resultat & chiffre1 * chiffre2 & chiffre2
chiffre1 = chiffre2
Else
chiffre1 = Left(texte, 1)
resultat = chiffre1
End If
Next numCar
f_challenge424 = resultat
End Function