Write the numbers in expanded form. For example 783 = 700+80+3 31045 = 30000+1000+40+5
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 252
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert Numbers to Expanded Form with Power Query
Power Query solution 1 for Convert Numbers to Expanded Form, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.AddColumn(
Source,
"Custom",
each Text.Combine(
List.Reverse(
List.Select(
List.Transform(
{0 .. Number.IntegerDivide(Number.Log10([Number]), 1)},
(n) =>
let
p = Number.Power(10, n)
in
Text.From(Number.Mod(Number.IntegerDivide([Number], p), 10) * p)
),
each _ > "0"
)
),
"+"
)
)
in
AnsPower Query solution 2 for Convert Numbers to Expanded Form, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Number],
r = (n) => Number.RoundDown(n),
f = (n) =>
let
c = Number.Power(10, r(Number.Log10(n))),
t = r(n / c) * c,
i = n - t
in
Text.From(t) & (if i > 0 then "+" & @f(i) else ""),
S = List.Transform(Source, f)
in
SPower Query solution 3 for Convert Numbers to Expanded Form, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each Text.Combine(
List.Select(
List.Accumulate(
List.Reverse(Text.ToList(Text.From([Number]))),
{},
(s, c) => {Text.TrimStart(c & Text.Repeat("0", List.Count(s)), "0")} & s
),
each _ <> ""
),
"+"
)
)
in
SPower Query solution 4 for Convert Numbers to Expanded Form, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
T = Text.From([Number]),
L = Text.Length(T) - 1,
G = List.Transform(
{0 .. L},
(f) =>
[t = Text.At(T, f) & Text.Repeat("0", L - f), r = if Text.At(T, f) = "0" then null else t][
r
]
),
R = Text.Combine(G, "+")
][R]
)
in
ReturnPower Query solution 5 for Convert Numbers to Expanded Form, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
Text = Text.From([Number]),
Len = Text.Length(Text),
RevSeq = List.Reverse({1 .. Len}),
Transform = List.Transform(
RevSeq,
(f) => Text.From(Number.From(Text.At(Text, Len - f)) * Number.Power(10, f - 1))
),
Filter = List.Select(Transform, (f) => f <> "0"),
Combine = Text.Combine(Filter, "+"),
Return = if [Number] = 0 then "0" else Combine
][Return]
)
in
ReturnPower Query solution 6 for Convert Numbers to Expanded Form, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = List.Transform(Text.ToList(Text.From([Number])), Number.From),
b = List.Reverse({0 .. List.Count(a) - 1}),
c = List.Zip({a, b}),
d = List.Select(
List.Transform(c, each Text.From(_{0} * Number.Power(10, _{1}))),
each _ <> "0"
)
in
Text.Combine(d, "+")
)[[Answer]]
in
SolPower Query solution 7 for Convert Numbers to Expanded Form, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = [Number],
b = Text.ToList(Text.From(a)),
c = List.Zip({b, List.Reverse({1 .. List.Count(b)})}),
d = List.Transform(c, (x) => Text.PadEnd(x{0}, x{1}, "0")),
e = Text.Combine(List.Select(d, each Number.From(_) <> 0), "+")
][e]
)
in
resPower Query solution 8 for Convert Numbers to Expanded Form, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Numbers"]}[Content],
Expanded = Table.AddColumn(
Source,
"Expanded",
each [
a = Text.ToList(Text.From([Number])),
b = List.Count(a) - 1,
c = List.Select(
List.Transform({0 .. b}, each a{_} & Text.Repeat("0", b - _)),
each Number.From(_) > 0
),
d = Text.Combine(c, "+")
][d]
)
in
ExpandedSolving the challenge of Convert Numbers to Expanded Form with Excel
Excel solution 1 for Convert Numbers to Expanded Form, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A9,
LAMBDA(a,
LET(l,
LEN(
a
),
n,
SEQUENCE(
l
),
m,
MID(
a,
n,
1
)*10^(l-n),
TEXTJOIN(
"+",
,
IF(
m,
m,
""
)
))))Excel solution 2 for Convert Numbers to Expanded Form, proposed by Rick Rothstein:
=MAP(A2:A9,
LAMBDA(x,
LET(s,
SEQUENCE(
LEN(
x
)
),
n,
MID(
x,
s,
1
)*10^(LEN(
x
)-s),
TEXTJOIN(
"+",
,
FILTER(
n,
n
)
))))
EDIT NOTE
--------------------
One character shorter...
=MAP(A2:A9,
LAMBDA(x,
LET(c,
LEN(
x
),
s,
SEQUENCE(
c
),
n,
MID(
x,
s,
1
)*10^(c-s),
TEXTJOIN(
"+",
,
FILTER(
n,
n
)
))))Excel solution 3 for Convert Numbers to Expanded Form, proposed by John V.:
=MAP(A2:A9,
LAMBDA(x,
LET(c,
LEN(
x
),
s,
SEQUENCE(
c
),
n,
MID(
x,
s,
1
)*10^(c-s),
TEXTJOIN(
"+",
,
IF(
n,
n,
""
)
))))Excel solution 4 for Convert Numbers to Expanded Form, proposed by محمد حلمي:
=MAP(A2:A9,
LAMBDA(a,
LET(i,
LEN(
a
),
s,
SEQUENCE(
i
),
x,
MID(
a+0,
s,
1
),
c,
10^(i-s),
TEXTJOIN(
"+",
,
IF(
x*c,
x*c,
""
)
))))Excel solution 5 for Convert Numbers to Expanded Form, proposed by محمد حلمي:
=MAP(A2:A9,
LAMBDA(a,
LET(i,
LEN(
a
),
s,
SEQUENCE(
i
),
x,
MID(
a+0,
s,
1
),
TEXTJOIN("+",
,
IF(-x,
x*10^(i-s),
"")))))Excel solution 6 for Convert Numbers to Expanded Form, proposed by Kris Jaganah:
=MAP(A2:A9,LAMBDA(x,LET(a,SEQUENCE(LEN(x)),b,MID(x,a,1),c,b*10^(SORT(a,,-1)-1),TEXTJOIN("+",1,IF((c=0),"",c)))))Excel solution 7 for Convert Numbers to Expanded Form, proposed by Julian Poeltl:
=MAP(
A2:A9,
LAMBDA(
N,
LET(
L,
LEN(
N
),
SP,
MID(
N,
SEQUENCE(
L
),
1
),
IFERROR(
TEXTJOIN(
"+",
,
IF(
--SP>0,
SP&REPT(
0,
VSTACK(
SEQUENCE(
L-1,
,
L-1,
-1
),
0
)
),
""
)
),
N
)
)
)
)Excel solution 8 for Convert Numbers to Expanded Form, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
sq,
SEQUENCE(
LEN(
a
)
),
splt,
MID(
a,
sq,
1
),
rvsq,
SORT(
sq,
,
-1
) - 1,
pv,
splt * 10 ^ rvsq,
fltr,
FILTER(
pv,
pv,
0
),
r,
TEXTJOIN(
"+",
1,
fltr
),
r
)
)
)Excel solution 9 for Convert Numbers to Expanded Form, proposed by Timothée BLIOT:
=MAP(A2:A9,
LAMBDA(z,
LET(A,
LEN(
z
),
B,
SORT(MID(
z,
SEQUENCE(
A
),
1
)*(10^SEQUENCE(
A,
,
A-1,
-1
)),
,
-1),
TEXTJOIN(
"+",
,
FILTER(
B,
B<>0
)
))))Excel solution 10 for Convert Numbers to Expanded Form, proposed by Hussein SATOUR:
=MAP(A2:A9,
LAMBDA(x,
LET(
a,
LEN(
x
),
b,
SEQUENCE(
a
),
c,
MID(
x,
b,
1
) * 10^(a-b),
TEXTJOIN(
"+",
,
FILTER(
c,
c <> 0
)
))))Excel solution 11 for Convert Numbers to Expanded Form, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A9,
LAMBDA(a,
LET(_l,
LEN(
a
),
_s,
SEQUENCE(
,
_l
),
TEXTJOIN("+",
1,
IFERROR((1/MID(
a,
_s,
1
)/10^(_l-_s))^-1,
"")))))Excel solution 12 for Convert Numbers to Expanded Form, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
_m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
) + 0,
_n,
10 ^ SEQUENCE(
ROWS(
_m
),
,
ROWS(
_m
) - 1,
-1
),
TEXTAFTER(
CONCAT(
MAP(
_m,
_n,
LAMBDA(
a,
b,
IF(
a,
"+" & a * b,
""
)
)
)
),
"+"
)
)
)
)Excel solution 13 for Convert Numbers to Expanded Form, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(
num,
LET(
_d,
LEFT(
num,
SEQUENCE(
LEN(
num
)
)
),
_l,
RIGHT(
_d
),
_p,
POWER(
10,
LEN(
num
) - LEN(
_d
)
),
TEXTJOIN(
"+",
,
IF(
_l + 0,
_l * _p,
""
)
)
)
)
)Excel solution 14 for Convert Numbers to Expanded Form, proposed by Sunny Baggu:
=MAP(
A2:A9,
LAMBDA(a,
LET(
_m, MID(a, SEQUENCE(LEN(a)), 1),
_s, SORT(10 ^ SEQUENCE(LEN(a), , 0), , -1),
_list, _m * _s,
TEXTJOIN("+", , IF(_list, _list, ""))
)
)
)Excel solution 15 for Convert Numbers to Expanded Form, proposed by Abdallah Ally:
=MAP(A2:A9,LAMBDA(v,LET(a,v,b,LEN(a),c,MID(v,SEQUENCE(b),1),d,SEQUENCE(b,,b,-1),e,c&REPT(0,d-1),f,REDUCE("",e,LAMBDA(x,y,IF(--y<>0,x&"+"&y,x))),RIGHT(f,LEN(f)-1))))Excel solution 16 for Convert Numbers to Expanded Form, proposed by Abdallah Ally:
=MAP(
A2:A9,
LAMBDA(
v,
LET(
a,
v,
b,
LEN(
a
),
c,
MID(
v,
SEQUENCE(
b
),
1
)&REPT(
0,
SEQUENCE(
& b,
,
b,
-1
)-1
),
TEXTJOIN(
"+",
TRUE,
IF(
--c,
c,
""
)
)
)
)
)
TEXTJOIN with FILTER
=MAP(
A2:A9,
LAMBDA(
v,
LET(
a,
v,
b,
LEN(
a
),
c,
MID(
v,
SEQUENCE(
b
),
1
)&REPT(
0,
SEQUENCE(
b,
,
b,
-1
)-1
),
TEXTJOIN(
"+",
TRUE,
FILTER(
c,
--c
)
)
)
)
)Excel solution 17 for Convert Numbers to Expanded Form, proposed by Anshu Bantra:
=LAMBDA(
Num,
LET(
length,
LEN(
Num
),
split,
MID(
Num,
SEQUENCE(
length
),
1
),
multiplier,
10 ^ SEQUENCE(
length,
,
length - 1,
-1
),
TEXTJOIN(
"+",
,
split * multiplier
)
)
)(A1)Excel solution 18 for Convert Numbers to Expanded Form, proposed by Asheesh Pahwa:
=MAP(A2:A9,LAMBDA(x,LET(a,LEN(x),
b,SEQUENCE(a),
c,MID(x,b,1)&REPT(0,
SEQUENCE(a,,a,-1)-1), TEXTAFTER(CONCAT("+"&c),"+",1))))Excel solution 19 for Convert Numbers to Expanded Form, proposed by Charles Roldan:
=LET(r,
LAMBDA(
x,
REPLACE(
x,
1,
1,
)
),
r(MAP(A2:A9,
LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
IF(IFERROR(
x,
),
"+" & LEFT(
x
) & REPT(
"0",
LEN(
x
) - 1
) & g(
g
)(--r(
x
)),
)))))))Excel solution 20 for Convert Numbers to Expanded Form, proposed by Julien Lacaze:
=MAP(
A2:A9,
LAMBDA(
data,
LET(
l,
LEN(
data
),
s,
1*MID(
data,
SEQUENCE(
l
),
1
),
TEXTJOIN(
"+",
,
FILTER(
s&REPT(
"0",
SEQUENCE(
l,
,
l-1,
-1
)
),
s>0
)
)
)
)
)
How it works :
REPT() is used to repeat "0" char depending on Char position ;
FILTER()Excel solution 21 for Convert Numbers to Expanded Form, proposed by Daniel Garzia:
=MAP(A2:A9,LAMBDA(x,LET(n,LEN(x),s,SEQUENCE(n),f,MID(x,s,1)&REPT(0,n-s),TEXTJOIN("+",,FILTER(f,-f)))))Excel solution 22 for Convert Numbers to Expanded Form, proposed by Anup Kumar:
=MAP(
A2:A9,
LAMBDA(
a,
LET(
nm,
a,
nmLen,
LEN(
nm
),
nmArr,
MID(
nm,
SEQUENCE(
nmLen
),
1
),
zerosArr,
nmArr&REPT(
"0",
SEQUENCE(
nmLen,
,
nmLen-1,
-1
)
),
TEXTJOIN(
"+",
TRUE,
FILTER(
zerosArr,
zerosArr*1<>0
)
)
)
)
)Excel solution 23 for Convert Numbers to Expanded Form, proposed by Md Ismail Hosen:
=LAMBDA(
Numbers,
LET(
_fx_ForOne,
LAMBDA(
Number,
LET(
_ToChars,
LAMBDA(
InputText,
IF(
InputText = "",
"",
MID(
InputText,
SEQUENCE(
LEN(
InputText
)
),
1
)
)
),
_Multiplier,
SORT(
VSTACK(
1,
SCAN(
1,
SEQUENCE(
LEN(
Number
) - 1
),
LAMBDA(
Acc,
Curr,
Acc * 10
)
)
),
1,
-1
),
_Chars,
_ToChars(
Number
),
_Multiplied,
_Multiplier * _Chars,
_Result,
IF(
LEN(
Number
) = 1,
Number,
TEXTJOIN(
"+",
TRUE,
FILTER(
_Multiplied,
_Multiplied <> 0
)
)
),
_Result
)
),
_Result,
MAP(
Numbers,
_fx_ForOne
),
_Result
)
)(A2:A9)Excel solution 24 for Convert Numbers to Expanded Form, proposed by Rayan S.:
=MAP(
A2:A9,
LAMBDA(
arr,
TEXTJOIN(
"+",
,
LET(
x,
LET(
s,
MID(
arr,
SEQUENCE(
LEN(
arr
)
),
1
),
s&REPT(
0,
SEQUENCE(
LEN(
arr
),
,
LEN(
arr
)-1,
-1
)
)
)+0,
FILTER(
x,
x<>0
)
)
)
)
)Excel solution 25 for Convert Numbers to Expanded Form, proposed by Amardeep Singh:
=MAP(A2:A9,
LAMBDA(m,
LET(
s,
SEQUENCE(
LEN(
m
)
),
d,
MID(
m,
s,
1
)*10^(SORT(
s,
,
-1
)-1),
TEXTJOIN(
"+",
,
FILTER(
d,
d>0,
0
)
))))Excel solution 26 for Convert Numbers to Expanded Form, proposed by Mungunbayar Bat-Ochir:
= LET(
input;A5;
seq;SEQUENCE(LEN(input));
chars;MID(input;seq;1);
arr;IF(chars<>"0";chars & REPT(0;SORT(seq;;-1)-1);"");
joined;TEXTJOIN("+";TRUE;arr);
joined
)Excel solution 27 for Convert Numbers to Expanded Form, proposed by Miguel Angel Franco García:
=LET(
a;
EXTRAE(
A2;
SECUENCIA(
;
LARGO(
A2
)
);
1
);
b;
SECUENCIA(
;
LARGO(
A2
);
LARGO(
A2
);
-1
)-1;
c;
a&REPETIR(
0;
b
);
UNIRCADENAS(
"+";
VERDADERO;
SI(
IZQUIERDA(
c
)="0";
"";
c
)
)
)Excel solution 28 for Convert Numbers to Expanded Form, proposed by Hussain Ali Nasser:
=MAP(
A2:A9,
LAMBDA(_range,
LET(
_len,
LEN(
_range
),
_split,
MID(
_range,
SEQUENCE(
LEN(
_range
)
),
1
),
_power,
10 ^ (SEQUENCE(
_len,
,
_len,
-1
) - 1),
SUBSTITUTE(
TEXTJOIN(
"+",
,
_split * _power
),
"+0",
""
)
)
)
)Excel solution 29 for Convert Numbers to Expanded Form, proposed by Deepak Dalal:
=MAP(A2:A9,
LAMBDA(c,
TEXTJOIN("+",
1,
LET(b,
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
MAP(b,
SEQUENCE(
COUNTA(
b
),
,
COUNTA(
b
)-1,
-1
),
LAMBDA(r,
a,
IF(r="0",
"",
r*(10^a))))))))Excel solution 30 for Convert Numbers to Expanded Form, proposed by Vishal Mishra:
=
MAP(A2:A9,
LAMBDA(Number,
LET(
LenOfNumber,LEN(Number),
Range,10^SEQUENCE(LenOfNumber,,0),
DigitValue,MID(Number,SEQUENCE(LenOfNumber,,LenOfNumber,-1),1),
CalValue, Range * DigitValue,
TEXTJOIN(
"+",
1,
SORT(
FILTER(CalValue, CalValue>0),,
-1
)
)
)
)
)Excel solution 31 for Convert Numbers to Expanded Form, proposed by Niels de Winter:
=TEXTJOIN(
"+";
TRUE;
INDEX(
IF(MID(
A2;
SEQUENCE(
LEN(
A2
);
1;
LEN(
A2
);
-1
);
1
)* (10 ^ (SEQUENCE(
LEN(
A2
);
1;
1;
1
) -1)) = 0;
"";
MID(
A2;
SEQUENCE(
LEN(
A2
);
1;
LEN(
A2
);
-1
);
1
)* (10 ^ (SEQUENCE(
LEN(
A2
);
1;
1;
1
) -1)));
SEQUENCE(
LEN(
A2
);
1;
LEN(
A2
);
-1
)))Solving the challenge of Convert Numbers to Expanded Form with Python in Excel
Python in Excel solution 1 for Convert Numbers to Expanded Form, proposed by Alejandro Campos:
def expanded_form(number):
digits = list(str(number))
expanded_parts = [
str(int(digit) * 10**i) for i, digit in enumerate(digits[::-1]) if digit != '0'][::-1]
return '+'.join(expanded_parts)
numbers = xl("A2:A9")[0]
[expanded_form(num) for num in numbers]
Solving the challenge of Convert Numbers to Expanded Form with Excel VBA
Excel VBA solution 1 for Convert Numbers to Expanded Form, proposed by Nicolas Micot:
VBA solution:
Function f_expanded(nombre) As String
Dim zeros As String
Dim chiffre As Integer
For i = Len(nombre) To 1 Step -1
chiffre = Mid(nombre, i, 1)
If chiffre > 0 Then
f_expanded = chiffre & zeros & IIf(f_expanded <> "", "+", "") & f_expanded
End If
zeros = zeros & "0"
Next i
End Function
&&