Add Number1 and Number 2 together. Some of the numbers / final result will be greater than 15 significant digits which is the limit for Excel. Hence, Number1, Number2 and Answers are in text format.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 122
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Add Large Text Numbers with Power Query
Power Query solution 1 for Add Large Text Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs = List.Transform(
Table.ToRows(Source),
(ns) =>
List.Accumulate(
{1 .. List.Max(List.Transform(ns, Text.Length))},
"",
(s, l) =>
Text.From(
List.Sum(List.Transform(ns, each Number.From(Text.Start(Text.End("0" & _, l), 1))))
+ Number.From(Text.Length(s) >= l)
)
& Text.End(s, l - 1)
)
)
in
RsPower Query solution 2 for Add Large Text Numbers, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs = List.Transform(
Table.ToRows(Source),
(ns) =>
List.Accumulate(
{1 .. List.Max(List.Transform(ns, Text.Length))},
"",
(s, l) =>
Text.From(
Number.From(Text.Start(Text.End("0" & ns{0}, l), 1))
+ Number.From(Text.Start(Text.End("0" & ns{1}, l), 1))
+ Number.From(Text.Length(s) >= l)
)
& Text.End(s, l - 1)
)
)
in
RsPower Query solution 3 for Add Large Text Numbers, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "LongAddition"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each List.Reverse(Splitter.SplitTextByRepeatedLengths(15, true)([Number1]))
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom2",
each List.Reverse(Splitter.SplitTextByRepeatedLengths(15, true)([Number2]))
),
#"Added Custom2" = Table.AddColumn(
#"Added Custom1",
"Sum",
each [
n1 = Number.From([Custom]{0}) + Number.From([Custom2]{0}),
t1 = Text.From(n1),
s1 = Text.End(t1, 15),
r1 = Number.From(Text.Replace(t1, s1, "")),
n2 = List.Sum({Number.From([Custom]{1}?), Number.From([Custom2]{1}?), r1}),
t2 = Text.From(n2),
s2 = Text.End(t2, 15),
r2 = try Number.From(Text.Replace(t2, s2, "")) otherwise null,
n3 = List.Sum({Number.From([Custom]{2}?), Number.From([Custom2]{2}?), r2}),
t3 = Text.From(n3),
s3 = Text.End(t3, 15),
f = (if s3 = null then "" else s3) & (if s2 = null then "" else s2) & s1
][f]
)[[Sum]]
in
#"Added Custom2"Power Query solution 4 for Add Large Text Numbers, proposed by Jan Willem Van Holst:
let
Source = YourData,
#"Added Custom" = Table.AddColumn(
Source,
"MyResult",
each
let
length = List.Max({Text.Length([Number1]), Text.Length([Number2])}) + 1,
num1Pad = Text.PadStart([Number1], length, "0"),
num2Pad = Text.PadStart([Number2], length, "0"),
gen = List.Generate(
() => [i = 0, cum = "", over = 0, len = length],
each [i] <= length,
each
let
sum = Number.From(Text.At(num1Pad, length - 1 - [i]))
+ Number.From(Text.At(num2Pad, length - 1 - [i]))
+ [over],
digit = Text.From(Number.Mod(sum, 10))
in
[i = [i] + 1, cum = digit & [cum], over = Number.IntegerDivide(sum, 10), len = [len]]
),
result = List.Last(gen)[cum]
in
if Text.StartsWith(result, "0") then Text.RemoveRange(result, 0) else result
)
in
#"Added Custom"Solving the challenge of Add Large Text Numbers with Excel
Excel solution 1 for Add Large Text Numbers, proposed by Bo Rydobon 🇹🇭:
=BYROW(A2:B13,
LAMBDA(x,
REDUCE("",
SEQUENCE(
MAX(
LEN(
x
)
)
),
LAMBDA(a,
n,
SUM(--(LEFT(
RIGHT(
REPT(
0,
30
)&x,
n
)
)),
N(
LEN(
a
)>n-1
))&RIGHT(
a,
n-1
)))))
and short Version2
=BYROW(A2:B13,
LAMBDA(z,
REDUCE("",
SEQUENCE(
MAX(
LEN(
z
)
)
),
LAMBDA(a,
n,
SUM(
--LEFT(
RIGHT(
0&z,
n
)
)
)+(LEN(
a
)>=n)&RIGHT(
a,
n-1
)))))Excel solution 2 for Add Large Text Numbers, proposed by Bo Rydobon 🇹🇭:
=LET(
b,
BYROW(
A2:B13,
LAMBDA(
z,
TAKE(
REDUCE(
{"",
0},
MMULT(
--LEFT(
RIGHT(
REPT(
0,
42
)&z,
{14;28;42}
),
14
),
{1;1}
),
LAMBDA(
a,
v,
LET(
d,
DROP(
a,
,
1
),
HSTACK(
v+d&MID(
TAKE(
a,
,
1
),
1+d,
99
),
LEN(
v
)>14
)
)
)
),
,
1
)
)
),
MID(
b,
FIND(
LEFT(
--b
),
b
),
99
)
)Excel solution 3 for Add Large Text Numbers, proposed by John V.:
=BYROW(A2:B13,
LAMBDA(r,
REDUCE("",
{3;2;1},
LAMBDA(i,
x,
IFERROR(1/SUM(LEN(
i
)=1+14*(3-x),
--MID(
REPT(
0,
42-LEN(
r
)
)&r,
14*x-13,
14
))^-1,
"")&RIGHT(i,
14*(3-x))))))Excel solution 4 for Add Large Text Numbers, proposed by محمد حلمي:
=LET(
aa,
A2:A13,
bb,
B2:B13,
r,
RIGHT(
aa,
13
)+RIGHT(
bb,
13
),
a,
IFERROR(
LEFT(
r,
IF(
LEN(
r
)=14,
1,
0
)
)+0,
),
oo,
LAMBDA(
a,
x,
IFERROR(
RIGHT(
LEFT(
a,
LEN(
a
)-x
),
13
),
)
),
vv,
a+oo(
aa,
13
)+oo(
bb,
13
),
nn,
IF(
LEN(
vv
)>13,
LEFT(
RIGHT(
vv,
13
)+a
)
)+RIGHT(
oo(
aa,
26
)+oo(
bb,
26
),
13
),
ee,
RIGHT(
oo(
aa,
13
)+oo(
bb,
13
),
13
)+IFERROR(
LEFT(
r,
IF(
LEN(
r
)=14,
1,
0
)
)+0,
),
IF(
nn,
nn,
""
)&IF(
ee,
ee,
""
)&RIGHT(
r,
13
)
)Excel solution 5 for Add Large Text Numbers, proposed by محمد حلمي:
=TEXT(
A2:A13+B2:B13,
"@"
)Excel solution 6 for Add Large Text Numbers, proposed by Kris Jaganah:
=LET(
a,
A2:A13,
b,
B2:B13,
c,
IF(
LEN(
a
)>LEN(
b
),
a,
b
),
d,
IF(
LEN(
a
)LEN(
g
),
RIGHT(
m,
LEN(
m
)-1
),
m
),
q,
IF(
LEN(
m
)<>LEN(
g
),
n+1,
n
),
o&q&p
) Excel solution 7 for Add Large Text Numbers, proposed by Julian Poeltl:
=MAP(
A2:A13,
B2:B13,
LAMBDA(
N,
NN,
LET(
LN,
LEN(
N
),
LNN,
LEN(
NN
),
M,
MAX(
LN,
LNN
),
R,
LAMBDA(
A,
MID(
A,
SEQUENCE(
@LEN(
A
),
,
@LEN(
A
),
-1
),
1
)
),
SP,
LAMBDA(
A,
EXPAND(
R(
A
),
M,
,
0
)
),
SPN,
SP(
N
),
SPNN,
SP(
NN
),
P,
SPN+SPNN,
PE,
EXPAND(
P,
M+1,
,
0
),
LP,
LEN(
P
),
C,
RIGHT(
PE,
1
)+VSTACK(
0,
IF(
LP=2,
1,
0
)
),
CC,
RIGHT(
C,
1
)+DROP(
VSTACK(
0,
IF(
LEN(
C
)=2,
1,
0
)
),
-1
),
CCC,
RIGHT(
CC,
1
)+DROP(
VSTACK(
0,
IF(
LEN(
CC
)=2,
1,
0
)
),
-1
),
E,
IF(
TAKE(
CCC,
-1
)=0,
CONCAT(
DROP(
CCC,
-1
)
),
CONCAT(
CCC
)
),
CONCAT(
R(
E
)
)
)
)
)Excel solution 8 for Add Large Text Numbers, proposed by Aditya Kumar Darak 🇮🇳:
=BYROW(
A2:B13,
LAMBDA(a,
LET(
l,
LEN(
a
),
mx,
MAX(
l
),
jn,
REPT(
0,
mx - l
) & a,
e,
LAMBDA(
x,
y,
CONCAT(
TEXT(
SUM(
--RIGHT(
LEFT(
jn,
mx - y
)
)
) + LEFT(
x
),
"00"
),
RIGHT(
x,
y
)
)
),
f,
REDUCE(
0,
SEQUENCE(
mx,
,
0
),
e
),
r,
RIGHT(f,
LEN(
f
) - (LEFT(
f
) = "0")),
r
)
)
)Excel solution 9 for Add Large Text Numbers, proposed by Timothée BLIOT:
=LET(
A,
MID(
A2,
SEQUENCE(
LEN(
A2
),
,
LEN(
A2
),
-1
),
1
),
B,
MID(
B2,
SEQUENCE(
LEN(
B2
),
,
LEN(
B2
),
-1
),
1
),
Plus,
LAMBDA(
n,
m,
LET(
R,
MAX(
ROWS(
n
),
ROWS(
m
)
)+1,
S,
LAMBDA(
u,
VSTACK(
u,
SEQUENCE(
R-ROWS(
u
)
)*0
)
),
S(
n
)+S(
m
)
)
),
D,
Plus(
B,
A
),
Q,
LAMBDA(
me,
z,
LET(
K,
LAMBDA(
z,
VSTACK(
0,
DROP(
MAP(
z,
LAMBDA(
v,
QUOTIENT(
v,
10
)
)
),
-1
)
)
),
M,
LAMBDA(
z,
K,
MAP(
z,
& K,
LAMBDA(
d,
e,
MOD(
d,
10
)+e
)
)
),
newZ,
M(
z,
K(
z
)
),
IF(
SUM(
K(
z
)
)=0,
newZ,
me(
me,
newZ
)
)
)
),
F,
Q(
Q,
D
),
G,
TOCOL(
IF(
TAKE(
F,
-1
)=0,
TOCOL(
DROP(
F,
-1
),
3
),
F
),
3
),
H,
CONCAT(
SORTBY(
G,
SEQUENCE(
ROWS(
G
)
),
-1
)
),
H
)Excel solution 10 for Add Large Text Numbers, proposed by Md. Zohurul Islam:
=BYROW(
A2:B13,
LAMBDA(
x,
REDUCE(
"",
SEQUENCE(
MAX(
LEN(
x
)
),
,
0
),
LAMBDA(
p,
q,
LET(
a,
RIGHT(
p,
q
),
b,
ABS(
LEN(
p
)>q
),
d,
ABS(
LEFT(
RIGHT(
0&x,
q+1
)
)
),
e,
SUM(
d,
b
)&a,
e
)
)
)
)
)Excel solution 11 for Add Large Text Numbers, proposed by Charles Roldan:
=BYROW(
A2:B13,
LAMBDA(
Addends,
REDUCE(
"",
SEQUENCE(
MAX(
LEN(
Addends
)
),
,
0
),
LAMBDA(
textSum,
j,
LET(
CutoffSum,
RIGHT(
textSum,
j
),
CarryOne,
LEN(
textSum
) > j,
jthDigits,
--LEFT(
RIGHT(
0&Addends,
j + 1
)
),
SUM(
jthDigits,
CarryOne
)&CutoffSum
)
)
)
)
)Excel solution 12 for Add Large Text Numbers, proposed by Guillermo Arroyo:
=MAP(A2:A13,
B2:B13,
LAMBDA(a,
b,
TAKE(TEXTSPLIT(REDUCE("|0",
LET(
f,
LAMBDA(
i,
j,
MID(
i,
SEQUENCE(
,
j,
j,
-1
),
1
)
),
BYCOL(
IFNA(
--VSTACK(
f(
a,
LEN(
a
)
),
f(
b,
LEN(
b
)
)
),
0
),
LAMBDA(
x,
SUM(
x
)
)
)
),
LAMBDA(h,
g,
LET(s,
TEXTSPLIT(
h,
"|"
),
t,
g+(--INDEX(
s,
1,
2
)),
p,
MOD(
t,
10
),
q,
(t-p)/10,
r,
CONCAT(
p,
INDEX(
s,
1,
1
)
),
TEXTJOIN(
"|",
,
r,
q
)))),
"|"),
,
1)))
=MAP(
A2:A13,
B2:B13,
LAMBDA(
a,
b,
LET(
x,
LEN(
a
),
y,
LEN(
b
),
f,
LAMBDA(
i,
j,
h,
LET(
k,
IF(
h>=j,
h-j,
0
),
MID(
REPT(
"0",
k
)&i,
SEQUENCE(
,
j+k,
j+k,
-1
),
1
)
)
),
m,
f(
a,
x,
y
)+f(
b,
y,
x
),
t,
COLUMNS(
m
),
g,
LAMBDA(
s,
p,
q,
r,
IF(
r=0,
p,
LET(
z,
INDEX(
m,
1,
t-r+1
)+q,
s(
s,
CONCAT(
MOD(
z,
10
),
p
),
INT(
z/10
),
r-1
)
)
)
),
g(
g,
"",
0,
t
)
)
)
)Excel solution 13 for Add Large Text Numbers, proposed by Fábio Gatti:
=LAMBDA(Value1,
Value2,
LET(
_Chars,
14,
_Blocks,
4,
_fxNumber,
LAMBDA(
vNumber,
REPT(
"0",
_Chars*_Blocks-LEN(
vNumber
)
)&vNumber
),
_Value1,
_fxNumber(
Value1
),
_Value2,
_fxNumber(
Value2
),
_Seq,
SEQUENCE(
_Blocks
),
_SeqInv,
SEQUENCE(
_Blocks,
,
_Blocks,
-1
),
_fxNumbersArray,
LAMBDA(vNumber,
MID(vNumber,
(_SeqInv-1)*_Chars+1,
_Chars)),
_NumsValue1,
_fxNumbersArray(
_Value1
),
_NumsValue2,
_fxNumbersArray(
_Value2
),
_SumNums,
_NumsValue1+_NumsValue2,
_UpChars,
LEN(
_SumNums
)>_Chars,
_Add1,
(INDEX(
_UpChars,
_Seq-1
)*(_Seq>1)),
_Add1ToValue,
RIGHT(
_SumNums,
14
)+_Add1,
_NumsCorrectSeq,
INDEX(
_Add1ToValue,
_SeqInv
),
_LeftZeros,
IF(
_NumsCorrectSeq=0,
"",
_NumsCorrectSeq
),
_Result,
CONCAT(
_LeftZeros
),
_Result
)
)(A2,
B2)Excel solution 14 for Add Large Text Numbers, proposed by Tushar Mehta:
=LAMBDA(_arr1,
_arr2,
MAP(_arr1,
_arr2,
LAMBDA(_raw1,
_raw2,
LET(
maxLen,
INT((13+MAX(
LEN(
_raw1
),
LEN(
_raw2
)
))/14)*14,
_n1,
RIGHT(
REPT(
"0",
maxLen
)&_raw1,
maxLen
),
_n2,
RIGHT(
REPT(
"0",
maxLen
)&_raw2,
maxLen
),
_sum14,
LAMBDA(
_n1,
_n2,
carry,
f,
LET(
_sumIt,
RIGHT(
_n1,
14
)+RIGHT(
_n2,
14
)+carry,
_newCarry,
IF(
LEN(
_sumIt
)>14,
LEFT(
_sumIt,
1
),
"0"
),
_sumNoCarry,
RIGHT(
_sumIt,
14
),
IF(
LEN(
_n1
)>14,
f(
LEFT(
_n1,
LEN(
_n1
)-14
),
LEFT(
_n2,
LEN(
_n2
)-14
),
_newCarry,
f
)&_sumNoCarry,
""&_sumIt
)
)
),
_sum14(_n1,
_n2,
"0",
_sum14))
)))(A2:A16,
B2:B16)Solving the challenge of Add Large Text Numbers with Python in Excel
Python in Excel solution 1 for Add Large Text Numbers, proposed by Alejandro Campos:
df = xl("A1:B13", headers=True)
df['Answer'] = (df['Number1'].apply(int) + df['Number2'].apply(int)).apply(str)
df
Solving the challenge of Add Large Text Numbers with SQL
SQL solution 1 for Add Large Text Numbers, proposed by Zoran Milokanović:
1/2
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDERING
,D.NUMBER1
,D.NUMBER2
FROM DATA D
),
&&