Reverse the English alphabets in the strings. Non-alphabets should not be touched.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 360
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reverse Only English Alphabets with Power Query
Power Query solution 1 for Reverse Only English Alphabets, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList([Strings]),
b = List.Positions(a),
c = List.Zip({a, b}),
d = List.Select(c, each List.Contains({"A" .. "z"}, _{0})),
e = List.Reverse(List.Transform(d, each _{0})),
f = List.Transform({0 .. List.Count(e) - 1}, each {e{_}} & {d{_}{1}}),
g = List.Sort(List.Select(c, each not List.Contains({"A" .. "z"}, _{0})) & f, each _{1}),
h = Text.Combine(List.Transform(g, each _{0}))
in
h
)[[Answer]]
in
SolPower Query solution 2 for Reverse Only English Alphabets, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each
let
a = Text.ToList([Strings]),
b = List.Positions(a),
c = List.Select(List.Zip({a, b}), (x) => List.ContainsAny({x{0}}, {"a" .. "z", "A" .. "Z"})),
c1 = List.Select(
List.Zip({b, a}),
(x) => not List.ContainsAny({x{1}}, {"a" .. "z", "A" .. "Z"})
),
d = List.Transform(c, each _{1}),
e = List.Zip({d, List.Reverse(List.Transform(c, each _{0}))}),
f = List.ReplaceMatchingItems(b, e)
in
Text.Combine(List.ReplaceMatchingItems(f, c1))
)
in
resPower Query solution 3 for Reverse Only English Alphabets, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddAnswer = Table.AddColumn(
Source,
"Answer",
each [
z = {"A" .. "Z"} & {"a" .. "z"},
a = Text.ToList([Strings]),
b = List.Count(a),
c = Table.FromColumns({a, {1 .. b}}, {"Char", "Pos"}),
d = Table.SelectRows(c, each List.Contains(z, [Char])),
e = Table.SelectRows(c, each not List.Contains(z, [Char])),
f = Table.ToColumns(d),
g = List.Reverse(f{0}),
h = f{1},
i = Table.FromColumns({g, h}, {"Char", "Pos"}),
j = Table.Combine({i, e}),
k = Table.Sort(j, {"Pos", Order.Ascending}),
m = Table.SelectColumns(k, "Char"),
n = Table.ToColumns(m)
][n]
),
Expand = Table.ExpandListColumn(AddAnswer, "Answer"),
Extract = Table.TransformColumns(
Expand,
{"Answer", each Text.Combine(List.Transform(_, Text.From)), type text}
)
in
ExtractPower Query solution 4 for Reverse Only English Alphabets, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
a = Text.ToList(x),
b = List.Positions(a),
c = List.Zip({b, a}),
d = List.Select(c, each List.ContainsAny(_, {"A" .. "Z", "a" .. "z"})),
e = List.Reverse(List.Transform(d, each _{1})),
f = List.Transform(d, each _{0}),
g = List.Zip({f, e}) & List.Difference(c, d),
h = Text.Combine(Table.Sort(Table.FromRows(g), {{"Column1", 0}})[Column2], "")
in
h,
Sol = Table.AddColumn(Origen, "Answer Expected", each Fx([Strings]))
in
SolPower Query solution 5 for Reverse Only English Alphabets, proposed by Arden Nguyen, CPA:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Strings", type text}}),
alphabet = List.Buffer({"A" .. "Z"} & {"a" .. "z"}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Solution",
each
let
to_list = List.Buffer(Text.ToList([Strings])),
non_alpha = List.RemoveMatchingItems(to_list, alphabet),
split_alpha = Text.SplitAny([Strings], Text.Combine(non_alpha)),
groups = List.Transform(split_alpha, Text.Length),
reverse_alphabet = Text.Combine(List.Reverse(Text.ToList(Text.Combine(split_alpha)))),
split_reversed_by_group = Splitter.SplitTextByLengths(groups)(reverse_alphabet),
add_non_alpha = Combiner.CombineTextByEachDelimiter(non_alpha)(split_reversed_by_group)
in
add_non_alpha
)
in
#"Added Custom"Solving the challenge of Reverse Only English Alphabets with Excel
Excel solution 1 for Reverse Only English Alphabets, proposed by John V.:
=MAP(
A2:A13,
LAMBDA(
z,
LET(
s,
SEQUENCE(
LEN(
z
)
),
t,
MID(
z,
s,
1
),
c,
t<"a",
b,
IF(
c,
"",
s
),
CONCAT(
IF(
c,
t,
INDEX(
t,
MAP(
s,
LAMBDA(
x,
LARGE(
b,
COUNT(
TAKE(
b,
x
)
)
)
)
)
)
)
)
)
)
)Excel solution 2 for Reverse Only English Alphabets, proposed by Kris Jaganah:
=MAP(A2:A13,
LAMBDA(x,
LET(a,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
a,
1
),
c,
CODE(
UPPER(
b
)
),
d,
FILTER(HSTACK(
a,
b
),
(c>64)*(c<91)),
e,
TAKE(
d,
,
1
),
CONCAT(
IFNA(
XLOOKUP(
a,
e,
SORTBY(
TAKE(
d,
,
-1
),
-e
)
),
b
)
))))Excel solution 3 for Reverse Only English Alphabets, proposed by Julian Poeltl:
=MAP(A2:A13,
LAMBDA(S,
LET(SP,
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
),
C,
CODE(
SP
),
L,
(C>96)*(C<123)+(C>64)*(C<91),
R,
SCAN(
0,
L,
LAMBDA(
A,
B,
A+B
)
),
F,
FILTER(
SP,
L
),
RV,
CHOOSEROWS(
F,
SEQUENCE(
ROWS(
F
),
,
ROWS(
F
),
-1
)
),
CONCAT(
IF(
L,
INDEX(
RV,
R
),
SP
)
))))Excel solution 4 for Reverse Only English Alphabets, proposed by Timothée BLIOT:
=MAP(
A2:A13,
LAMBDA(
z,
LET(
I,
INDEX,
A,
LEN(
z
),
B,
SEQUENCE(
A
),
C,
MID(
z,
B,
1
),
D,
REGEXTEST(
C,
"[A-Za-z]"
),
E,
FILTER(
C,
D
),
F,
SCAN(
0,
B,
LAMBDA(
w,
v,
IF(
I(
D,
v
),
w+1,
w
)
)
),
CONCAT(
MAP(
B,
LAMBDA(
x,
IF(
I(
D,
x
),
I(
SORTBY(
E,
SEQUENCE(
ROWS(
E
)
),
-1
),
I(
F,
x
)
),
I(
C,
x
)
)
)
)
)
)
)
)Excel solution 5 for Reverse Only English Alphabets, proposed by Sunny Baggu:
=MAP(
A2:A13,
LAMBDA(
t,
LET(
_s,
SEQUENCE(
LEN(
t
)
),
_m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
_c,
MAP(
UPPER(
_m
),
LAMBDA(
a,
AND(
CODE(
a
) >= 65,
CODE(
a
) <= 90
)
)
),
_a1,
FILTER(
_m,
_c
),
_a2,
FILTER(
_s,
_c
),
_a3,
SORTBY(
_a1,
_a2,
-1
),
_b1,
FILTER(
_m,
NOT(
_c
)
),
_b2,
FILTER(
_s,
NOT(
_c
)
),
IFERROR(
CONCAT(
TAKE(
SORT(
HSTACK(
VSTACK(
_a3,
_b1
),
VSTACK(
_a2,
_b2
)
),
2,
),
,
1
)
),
CONCAT(
MID(
t,
LEN(
t
) + 1 - SEQUENCE(
LEN(
t
)
),
1
)
)
)
)
)
)Excel solution 6 for Reverse Only English Alphabets, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A13,
LAMBDA(
r,
LET(
s,
SEQUENCE(
LEN(
r
)
),
m,
MID(
r,
s,
1
),
c,
CODE(
m
),
e,
IF(
c<=64,
s
),
d,
FILTER(
s,
c>64
),
o,
SORT(
d,
,
-1
),
CONCAT(
INDEX(
m,
IFNA(
XLOOKUP(
s,
d,
o
),
e
)
)
)
)
)
)Excel solution 7 for Reverse Only English Alphabets, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A13,
LAMBDA(X,
LET(a,
SEQUENCE(
LEN(
X
)
),
b,
MID(
X,
a,
1
),
CONCAT(XLOOKUP(a,
VSTACK(
SORT(
FILTER(
a,
b>"9"
)
),
a
),
VSTACK(SORT(HSTACK(FILTER(
b,
b>"9"
),
SEQUENCE(SUM(--(b>"9")))),
2,
-1),
b))))))Solving the challenge of Reverse Only English Alphabets with R
R solution 1 for Reverse Only English Alphabets, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/360 Reverse alphabets only.xlsx", range = "A1:A13")
test = read_excel("Excel/360 Reverse alphabets only.xlsx", range = "B1:B13")
reverse_alpha = function(word) {
chars = strsplit(word, "")[[1]]
pos = which(chars %in% c(letters, LETTERS))
alphas = chars[pos]
rev_alphas = rev(alphas)
chars[pos] = rev_alphas
processed = paste(chars, collapse = "")
return(processed)
}
output = input %>%
mutate(`Answer Expected` = map_chr(Strings, reverse_alpha))
Solving the challenge of Reverse Only English Alphabets with Excel VBA
Excel VBA solution 1 for Reverse Only English Alphabets, proposed by Hiran de Silva FCMA:
Sub DoExcelBI360()
Dim strTempString As String
Dim strSourceString As String
strSourceString = ActiveCell.Offset(0, -2).Value
For n = 1 To Len(strSourceString)
If IsAlphabetic(Mid(strSourceString, n, 1)) Then
strTempString = strTempString + Mid(strSourceString, n, 1)
End If
Next n
m = Len(strTempString)
For n = 1 To Len(strSourceString)
If IsAlphabetic(Mid(strSourceString, n, 1)) Then
strResultString = strResultString + Mid(strTempString, m, 1)
m = &m - 1
Else
strResultString = strResultString + Mid(strSourceString, n, 1)
End If
Next n
ActiveCell.Value = strResultString
End Sub
Function IsAlphabetic(strChar As String) As Boolean
IsAlphabetic = False
Select Case Asc(strChar)
Case 65 To 90, 97 To 122
IsAlphabetic = True
End Select
End Function
&&