Sort the digits of given numbers in column A in descending order. Ex. TwoThreeOne which is 231. Sorting in descending order will make it 321. Hence asnwer would be ThreeTwoOne.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 311
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort the digits of given numbers in descending order with Power Query
Power Query solution 1 for Sort the digits of given numbers in descending order, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each List.Accumulate(
{"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"},
"",
(s, c) => Text.Repeat(c, List.Count(Text.PositionOf([Number], c, 2))) & s
)
)
in
SPower Query solution 2 for Sort the digits of given numbers in descending order, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
N = {"Nine", "Eight", "Seven", "Six", "Five", "Four", "Three", "Two", "One", "Zero"},
S = Table.TransformRows(
Source,
each Text.Combine(
let
i = [Number],
L = List.Sort
in
L(
Splitter.SplitTextByPositions(
L(List.Combine(List.Transform(N, (n) => Text.PositionOf(i, n, 2))))
)(i),
each List.PositionOf(N, _)
)
)
)
in
SPower Query solution 3 for Sort the digits of given numbers in descending order, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
N = {"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"},
S = Table.TransformRows(
Source,
each Text.Combine(
List.Sort(
Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Number]),
each 1 / List.PositionOf(N, _)
)
)
)
in
SPower Query solution 4 for Sort the digits of given numbers in descending order, proposed by Rick de Groot:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"RU85DoAwDPtLZ74BIwwwUTFG0KWVKq7nYxsilsZxYseNMXTlqGO6w9LEMF1lyEZGLfBop+UunTZbLSL7lA0DFnKQ+O60VbM2rduuB6ZiNKYnreFECJVmfphuTqiSANb53wWy19pTCegR/1qw1bqQB4NWeT/sf1KC5QE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t]
),
Result = Table.TransformColumns(
Source,
{
{
"Number",
each [
a = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})(_),
b = [
#"Nine" = 9,
#"Eight" = 8,
#"Seven" = 7,
#"Six" = 6,
#"Five" = 5,
#"Four" = 4,
#"Three" = 3,
#"Two" = 2,
#"One" = 1,
#"Zero" = 0
],
c = List.Sort(a, {each 1 / Record.Field(b, _)}),
d = Text.Combine(c)
][d]
}
}
)
in
ResultPower Query solution 5 for Sort the digits of given numbers in descending order, proposed by Rick de Groot:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"RU85DoAwDPtLZ74BIwwwUTFG0KWVKq7nYxsilsZxYseNMXTlqGO6w9LEMF1lyEZGLfBop+UunTZbLSL7lA0DFnKQ+O60VbM2rduuB6ZiNKYnreFECJVmfphuTqiSANb53wWy19pTCegR/1qw1bqQB4NWeT/sf1KC5QE=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Number = _t]
),
Result = Table.TransformColumns(
Source,
{
{
"Number",
each [
a = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})(_),
b = List.Sort(
a,
each List.PositionOf(
{"Nine", "Eight", "Seven", "Six", "Five", "Four", "Three", "Two", "One", "Zero"},
_
)
),
c = Text.Combine(b)
][c]
}
}
)
in
ResultPower Query solution 6 for Sort the digits of given numbers in descending order, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Orden = List.Reverse(
{"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"}
),
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Number]),
b = List.Sort(a, each List.PositionOf(Orden, _))
in
Text.Combine(b)
)[[Answer]]
in
SolPower Query solution 7 for Sort the digits of given numbers in descending order, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
list = {"Nine", "Eight", "Seven", "Six", "Five", "Four", "Three", "Two", "One", "Zero"},
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Combine(
List.Sort(
Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Number]),
each List.PositionOf(list, _)
)
)
)
in
resPower Query solution 8 for Sort the digits of given numbers in descending order, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9},
b = {"Zero", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"},
c = Table.ToRows(
Table.SplitColumn(
Origen,
"Number",
Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"}),
{"N1", "N2", "N3", "N4", "N5", "N6", "N7", "N8", "N9", "N10"}
)
),
d = List.Transform(
c,
each List.Sort(List.ReplaceMatchingItems(List.RemoveNulls(_), List.Zip({b, a})), 1)
),
Sol = Table.FromColumns(
{List.Transform(d, each Text.Combine(List.ReplaceMatchingItems(_, List.Zip({a, b})), ""))},
{"Expected Answer"}
)
in
SolPower Query solution 9 for Sort the digits of given numbers in descending order, proposed by Luke Jarych:
let
Source = Table1,
numbersToFind = {"Nine", "Eight", "Seven", "Six", "Five", "Four", "Three", "Two", "One", "Zero"},
Addcolumn = Table.AddColumn(
Source,
"Expected Answer",
each
let
a = Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Number]),
b = List.Sort(a, each List.PositionOf(numbersToFind, _)),
c = Text.Combine(b)
in
c
)
in
AddcolumnPower Query solution 10 for Sort the digits of given numbers in descending order, proposed by Challa Sai Kumar Reddy:
let
Sol = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Answer",
each Text.Combine(
List.Sort(Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"A" .. "Z"})([Number])),
""
)
)
in
SolSolving the challenge of Sort the digits of given numbers in descending order with Excel
Excel solution 1 for Sort the digits of given numbers in descending order, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
n,
MATCH(
"?"&TEXTSPLIT(
a,
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
),
b,
),
CONCAT(
INDEX(
b,
SORT(
n,
,
-1
)
)
)
)
)
)Excel solution 2 for Sort the digits of given numbers in descending order, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
w,
TEXTSPLIT(
"n e se si fi fo th tw o z",
,
" "
),
n,
TEXTSPLIT(
REDUCE(
,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
a,
x,
a&IF(
CODE(
x
)<91,
" "&x,
x
)
)
),
,
" "
),
CONCAT(
SORTBY(
n,
XMATCH(
n,
w,
-1
)
)
)
)
)
)Excel solution 3 for Sort the digits of given numbers in descending order, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
w,
TEXTSPLIT(
"nine eight seven six five four three two one zero",
,
" "
),
n,
TEXTSPLIT(
REDUCE(
,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
a,
x,
a&IF(
CODE(
x
)<91,
" "&x,
x
)
)
),
,
" "
),
CONCAT(
SORTBY(
n,
XMATCH(
n,
w
)
)
)
)
)
)Excel solution 4 for Sort the digits of given numbers in descending order, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(b,TEXTSPLIT(REDUCE(x,CHAR(ROW(65:90)),LAMBDA(a,v,SUBSTITUTE(a,v,0&v))),0),CONCAT(SORTBY(b,FIND(LEFT(b,2),"NiEiSeSiFiFoThTwOnZe"))))))Excel solution 5 for Sort the digits of given numbers in descending order, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
B,
LET(
X,
{"N",
"E",
"S",
"SI",
"F",
"FO",
"T",
"TW",
"O",
"Z"},
E,
TEXTSPLIT(
REDUCE(
B,
X,
LAMBDA(
A,
D,
SUBSTITUTE(
A,
D,
1&D
)
)
),
1
),
CONCAT(
SORTBY(
E,
XMATCH(
LEFT(
E,
2
),
X,
-1
)
)
)
)
)
)Excel solution 6 for Sort the digits of given numbers in descending order, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
b,
SEQUENCE(
10,
,
0
),
c,
LEFT(
a
),
d,
TEXTAFTER(
a,
c
),
e,
DROP(
TEXTSPLIT(
x,
,
c
),
1
),
f,
XLOOKUP(
e,
d,
a
),
g,
XLOOKUP(
f,
a,
b
),
CONCAT(
SORTBY(
f,
g,
-1
)
)
)
)
)Excel solution 7 for Sort the digits of given numbers in descending order, proposed by Timothée BLIOT:
=MAP(A2:A10, LAMBDA(z, LET(A,{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
B, --SORT(TEXTSPLIT(REDUCE(z,A,LAMBDA(x,y, SUBSTITUTE(x,y,XLOOKUP(y,A,SEQUENCE(10,,0))&","))),,",",1),,-1),
CONCAT(MAP(B,LAMBDA(x, XLOOKUP(x,SEQUENCE(10,,0),A)))))))Excel solution 8 for Sort the digits of given numbers in descending order, proposed by Hussein SATOUR:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
c,
SORTBY(
IF(
FIND(
a,
x
) > 0,
a
),
SEQUENCE(
10,
,
10,
-1
)
),
CONCAT(IFERROR(REPT(c,
(LEN(
x
) - LEN(
SUBSTITUTE(
x,
c,
""
)
))/LEN(
c
)),
"")))))Excel solution 9 for Sort the digits of given numbers in descending order, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
LET(
_col,
TOCOL(
MAP(
{"Zero"; "One"; "Two"; "Three"; "Four"; "Five"; "Six"; "Seven"; "Eight"; "Nine"},
LAMBDA(
a,
& CONCAT(
IF(
UNIQUE(
TOCOL(
SEARCH(
a,
x,
SEQUENCE(
LEN(
x
)
)
),
3
)
),
a
)
)
)
),
3
),
SORTBY(
_col,
-SEQUENCE(
ROWS(
_col
)
)
)
)
)
)
)Excel solution 10 for Sort the digits of given numbers in descending order, proposed by Abdallah Ally:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
x,
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
TEXTSPLIT(TRIM(CONCAT(IF((CODE(
b
)>64)*(CODE(
b
)<91),
" "&b,
b))),
" "),
CONCAT(
SORTBY(
c,
SWITCH(
c,
"Zero",
0,
"One",
1,
"Two",
2,
"Three",
3,
"Four",
4,
"Five",
5,
"Six",
6,
"Seven",
7,
"Eight",
8,
"Nine",
9
),
-1
)
))))Excel solution 11 for Sort the digits of given numbers in descending order, proposed by Md. Zohurul Islam:
=LET(
u,{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
v,SEQUENCE(10,,0),
w,SEQUENCE(26,,65),
z,MAP(A2:A10,LAMBDA(x,LET(
a,MID(x,SEQUENCE(LEN(x)),1),
b,CODE(a),
c,CONCAT(IF(ISNUMBER(XMATCH(b,w)),"-"&a,a)),
d,DROP(TEXTSPLIT(c,,"-"),1),
e,SORT(XLOOKUP(d,u,v),,-1),
f,CONCAT(XLOOKUP(e,v,u)),
f))),
z)Excel solution 12 for Sort the digits of given numbers in descending order, proposed by Asheesh Pahwa:
=LET(
a,
A2:A8,
c,
CHOOSE(
SEQUENCE(
9
),
"One",
"Two",
"Three",
"Four",
"Five",
"Six",
"Seven",
"Eight",
"Nine"
),
d,
REDUCE(
a,
CHAR(
SEQUENCE(
26,
,
65
)
),
LAMBDA(
x,
y,
TRIM(
SUBSTITUTE(
x,
y,
" "&y
)
)
)
),
MAP(
d,
LAMBDA(
z,
LET(
t,
TEXTSPLIT(
z,
,
""
),
CONCAT(
SORTBY(
t,
XLOOKUP(
t,
c,
SEQUENCE(
9
)
),
-1
)
)
)
)
)
)Excel solution 13 for Sort the digits of given numbers in descending order, proposed by Charles Roldan:
=LET(
Data,
A2:A10,
Atlas,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
MAP(
Data,
LAMBDA(
Number,
CONCAT(
INDEX(
Atlas,
-SORT(
-XMATCH(
TEXTSPLIT(
REPLACE(
Number,
1,
1,
),
,
LEFT(
Atlas
)
),
REPLACE(
Atlas,
1,
1,
)
)
)
)
)
)
)
)Excel solution 14 for Sort the digits of given numbers in descending order, proposed by JvdV -:
=REDUCE("",
{"Nine",
"Eight",
"Seven",
"Six",
"Five",
"Four",
"Three",
"Two",
"One",
"Zero"},
LAMBDA(x,
y,
x&REPT(y,
(LEN(
A2:A10
)-LEN(
SUBSTITUTE(
A2:A10,
y,
)
))/LEN(
y
))))Excel solution 15 for Sort the digits of given numbers in descending order, proposed by Julien Lacaze:
=LET(
n,
""&SEQUENCE(
10,
,
0
),
t,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
txt2num,
LAMBDA(
txt,
REDUCE(
txt,
t,
LAMBDA(
a,
v,
IF(
ISNUMBER(
FIND(
v,
a
)
),
SUBSTITUTE(
a,
v,
XMATCH(
v,
t
)-1
),
a
)
)
)
),
num2txt,
LAMBDA(
num,
REDUCE(
num,
n,
LAMBDA(
a,
v,
IF(
ISNUMBER(
FIND(
v,
a
)
),
SUBSTITUTE(
a,
v,
INDEX(
t,
XMATCH(
v,
n
)
)
),
a
)
)
)
),
num2txt(
MAP(
txt2num(
A2:A10
),
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
,
-1
)
)
)
)
)
)Excel solution 16 for Sort the digits of given numbers in descending order, proposed by Pieter de Bruijn:
=MAP(A2:A10,LAMBDA(a,LET(v,TEXTSPLIT(a,CHAR(ROW(65:90)),,1),w,TEXTSPLIT(a,v,,1),CONCAT(SORTBY(w&v,FIND(w&LEFT(v),"NiEiSeSiFiFoThTwOnZe"))))))
Improved with comments from محمد حلمي :
=MAP(A2:A10,LAMBDA(a,LET(v,TEXTSPLIT(a,CHAR(ROW(65:90)),,1),w,TEXTSPLIT(a,v,,1),CONCAT(SORTBY(w&v,FIND(w&v,"NineEightSevenSixFiveFourThreeTwoOneZero"))))))
original post:
=MAP(A2:A10,LAMBDA(a,LET(t,CHAR(ROW(65:90)),v,TEXTSPLIT(a,t,,1),w,TEXTSPLIT(a,LOWER(t),,1),CONCAT(SORTBY(w&v,FIND(w&v,"NineEightSevenSixFiveFourThreeTwoOneZero"))))))Excel solution 17 for Sort the digits of given numbers in descending order, proposed by Nicolas Micot:
=LET(_nombres;
{"Nine";
"Eight";
"Seven";
"Six";
"Five";
"Four";
"Three";
"Two";
"One";
"Zero"};
_cpt;
(NBCAR(
A2
)-NBCAR(
SUBSTITUE(
A2;
_nombres;
""
)
))/NBCAR(
_nombres
);
CONCAT(
REPT(
_nombres;
_cpt
)
))Excel solution 18 for Sort the digits of given numbers in descending order, proposed by Ziad A.:
=MAP(A2:A10,LAMBDA(a,LET(s,TOCOL(SPLIT(REGEXREPLACE(a,"[A-Z][a-z]+","$0-"),"-")),JOIN(,SORT(s,XMATCH(s,{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"})-1,)))))Excel solution 19 for Sort the digits of given numbers in descending order, proposed by Daniel Garzia:
=MAP(A2:A10,LAMBDA(x,LET(c,MID(x,SEQUENCE(LEN(x)),1),t,TEXTSPLIT(TRIM(CONCAT(IF(CODE(c)<91," "&c,c))),," "),CONCAT(SORTBY(t,XMATCH(t,{"Nine";"Eight";"Seven";"Six";"Five";"Four";"Three";"Two";"One";"Zero"}))))))Excel solution 20 for Sort the digits of given numbers in descending order, proposed by Anup Kumar:
=MAP(
A2:A10,
LAMBDA(
tx,
LET(
ar,
MID(
tx,
SEQUENCE(
LEN(
tx
),
,
1
),
1
),
nu,
TEXTSPLIT(
CONCAT(
SCAN(
"",
ar,
LAMBDA(
x,
y,
IF(
ISNUMBER(
FIND(
y,
CONCAT(
CHAR(
SEQUENCE(
26,
,
65
)
)
)
)
),
"|"&y,
y
)
)
)
),
,
"|"
),
CONCAT(
SORTBY(
nu,
XMATCH(
nu,
{"Nine";"Eight";"Seven";"Six";"Five";"Four";"Three";"Two";"One"}
)
)
)
)
)
)Excel solution 21 for Sort the digits of given numbers in descending order, proposed by Hazem Hassan:
=MAP(
REDUCE(
A2:A10,
CHAR(
SEQUENCE(
26,
,
65
)
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
" "&y
)
)
),
LAMBDA(
z,
CONCAT(
SORTBY(
TEXTSPLIT(
z,
,
" ",
1
),
MATCH(
TEXTSPLIT(
z,
,
" ",
1
),
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
0
)-1,
-1
)
)
)
)Excel solution 22 for Sort the digits of given numbers in descending order, proposed by Hazem Hassan:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
CHAR(
SEQUENCE(
26,
,
65
)
),
b,
TEXTSPLIT(
x,
,
LOWER(
a
),
1
),
s,
TEXTSPLIT(
x,
,
a,
1
),
d,
b&s,
CONCAT(
SORTBY(
d,
MATCH(
d,
{"Zero";"One";"Two";"Three";"Four";"Five";"Six";"Seven";"Eight";"Nine"},
0
)-1,
-1
)
)
)
)
)Solving the challenge of Sort the digits of given numbers in descending order with Python
Python solution 1 for Sort the digits of given numbers in descending order, proposed by Ujjawal Sinha:
import re
DIGITS = [
"Five" , "Six", "Seven", "Eight", "Nine",
]
def sort_digits(digits_str: str) -> str:
digits = re.findall("[A-Z][^A-Z]*", digits_str)
return "".join(
sorted(
digits,
key=DIGITS.index,
reverse=True,
)
)
Solving the challenge of Sort the digits of given numbers in descending order with Python in Excel
Python in Excel solution 1 for Sort the digits of given numbers in descending order, proposed by John V.:
Hi everyone!
[''.join(sorted(re.findall('[A-Z][^A-Z]*', i), key = n.index)) for i in xl("A2:A10")[0]]
Blessings!
Solving the challenge of Sort the digits of given numbers in descending order with R
R solution 1 for Sort the digits of given numbers in descending order, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(data.table)
library(readxl)
input = read_excel("Sort Text Numbers.xlsx", range = "A1:A10")
test = read_excel("Sort Text Numbers.xlsx", range = "B1:B10")
number_mappings <- function() {
"Five" = 5, "Six" = 6, "Seven" = 7, "Eight" = 8, "Nine" = 9)
}
word_to_number <- function(word) {
numbers <- number_mappings()
numbers[word]
}
name_mapping <- setNames(names(number_mappings()), number_mappings())
name_mapping[as.character(number)]
}
process_number_string <- function(num_string) {
digits <- map_int(words, word_to_&number)
sorted_digits <- sort(digits, decreasing = TRUE)
result_string <- paste0(unlist(sorted_words), collapse = "")
return(result_string)
}
result = input %>%
rowwise() %>%
mutate(remapped = map_chr(Number, process_number_string)) %>%
ungroup()
identical(test$`Expected Answer`, result$remapped)
&