Reverse only the vowels in the given words. Ex. ice – eci universal – anevirsul
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 279
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reverse Only Vowels in Word with Power Query
Power Query solution 1 for Reverse Only Vowels in Word, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each
let
v = Text.ToList("aeiouAEIOU"),
w = Text.ToList([Words]),
c = List.Reverse(List.Select(w, each List.Contains(v, _)))
in
Text.Combine(
List.Transform(
{1 .. List.Count(w)},
(n) =>
if List.Contains(v, w{n - 1}) then
c{List.Count(List.Select(List.FirstN(w, n), each List.Contains(v, _))) - 1}
else
w{n - 1}
)
)
)
in
AnsPower Query solution 2 for Reverse Only Vowels in Word, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][Words],
S = List.Transform(
Source,
each
let
p = Text.PositionOfAny(_, {"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"}, 2)
in
List.Accumulate(
List.Positions(p),
_,
(s, c) => Text.ReplaceRange(s, p{c}, 1, Text.At(_, List.Reverse(p){c}))
)
)
in
SPower Query solution 3 for Reverse Only Vowels in Word, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Vowels = {"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"},
Alphabets = {"a" .. "z", "A" .. "Z"},
Return = Table.AddColumn(
Source,
"Ans",
each [
VP = Text.PositionOfAny([Words], Vowels, 2),
AP = Text.PositionOfAny([Words], Alphabets, 2),
RP = List.ReplaceMatchingItems(AP, List.Zip({VP, List.Reverse(VP)})),
R = Text.Combine(List.Transform(RP, (f) => Text.At([Words], f)))
][R]
)
in
ReturnPower Query solution 4 for Reverse Only Vowels in Word, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.TransformColumns(
Source,
{
"Words",
each
let
a = Text.ToList(_),
b = List.PositionOfAny(
a,
{"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"},
Occurrence.All
),
c = List.Zip({b, List.Reverse(b)}),
d = List.ReplaceMatchingItems(List.Positions(a), c),
e = List.ReplaceMatchingItems(d, List.Zip({List.Positions(a), a}))
in
Text.Combine(e)
}
)
in
SolPower Query solution 5 for Reverse Only Vowels in Word, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Zip(
{
Text.PositionOfAny(
[Words],
{"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"},
Occurrence.All
),
List.Reverse(
Text.ToList(Text.Select([Words], {"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"}))
)
}
),
b = Text.PositionOfAny([Words], {"a" .. "z", "A" .. "Z"}, Occurrence.All),
c = List.ReplaceMatchingItems(b, a),
d = List.Zip({b, Text.ToList([Words])}),
e = Text.Combine(List.ReplaceMatchingItems(c, d))
][e]
)
in
resPower Query solution 6 for Reverse Only Vowels in Word, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "WordsTable"]}[Content],
ReverseVowels = Table.AddColumn(
Source,
"Reversed Vowels",
each
let
//List of characters
Chars = Text.ToList([Words]),
// List of all vowels
Vowels = {"a", "e", "i", "o", "u", "A", "E", "I", "O", "U"},
// Table of characters and original positions
CharTable = Table.FromRows(List.Zip({Chars, List.Positions(Chars)}), {"Char", "Pos"}),
// List of Vowel Records from CharTable
VowelRec = List.Select(Table.ToRecords(CharTable), each List.Contains(Vowels, [Char])),
// List of Vowel Records with reversed positions
VowelRec_Rev = Table.ToRecords(
Table.FromRows(
List.Zip(
{
List.Transform(VowelRec, each [Char]),
List.Reverse(List.PositionOfAny(Chars, Vowels, Occurrence.All))
}
),
{"Char", "Pos"}
)
),
// Replace Original Vowel Rows with reversed positions
Replace = Table.ReplaceMatchingRows(CharTable, List.Zip({VowelRec, VowelRec_Rev})),
Reorder = Text.Combine(Table.Sort(Replace, {{"Pos", Order.Ascending}})[Char])
in
Reorder,
type text
)
in
ReverseVowelsSolving the challenge of Reverse Only Vowels in Word with Excel
Excel solution 1 for Reverse Only Vowels in Word, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A11,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),m,MID(a,s,1),f,1-ISERR(SEARCH(m,"aeiou")),c,IF(f,-s),
CONCAT(SORTBY(SORTBY(m,c),SORTBY(s,-f))))))Excel solution 2 for Reverse Only Vowels in Word, proposed by John V.:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)
),
c,
MID(
x,
s,
1
),
b,
IF(
SEARCH(
c,
"aeiou"
),
s
),
m,
SORT(
HSTACK(
c,
b,
s
),
2
),
CONCAT(
SORTBY(
TAKE(
m,
,
1
),
IFERROR(
-SORT(
-b
),
DROP(
m,
,
2
)
)
)
)
)
)
)Excel solution 3 for Reverse Only Vowels in Word, proposed by محمد حلمي:
=MAP(A2:A11,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),
r,MID(a,s,1),v,SEARCH(r,"aeiou")^0*s,
IFERROR(CONCAT(SORTBY(SORTBY(r,-v),
VSTACK(TOCOL(v,2),TOCOL(s/ISERR(v),2)))),a))))Excel solution 4 for Reverse Only Vowels in Word, proposed by Kris Jaganah:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
a,
1
),
c,
{"a";"e";"i";"o";"u"},
d,
XLOOKUP(
b,
c,
c,
""
),
e,
FILTER(
HSTACK(
a,
d
),
d<>"",
""
),
CONCAT(
IFNA(
XLOOKUP(
a,
SORT(
TAKE(
e,
,
1
),
,
-1
),
TAKE(
e,
,
-1
)
),
b
)
)
)
)
)Excel solution 5 for Reverse Only Vowels in Word, proposed by Timothée BLIOT:
=MAP(
A2:A11,
LAMBDA(
z,
LET(
I,
INDEX,
S,
SEQUENCE(
LEN(
z
)
),
T,
MID(
z,
S,
1
),
V,
ISNUMBER(
FIND(
T,
"aeiouAEIOU"
)
),
D,
TOCOL(
SORTBY(
IF(
V,
T,
1/0
),
-S
),
3
),
R,
SCAN(
0,
S,
LAMBDA(
a,
x,
IF(
I(
V,
x
),
a+1,
a
)
)
),
CONCAT(
MAP(
S,
LAMBDA(
x,
IF(
I(
V,
x
),
I(
D,
I(
R,
x
)
),
I(
T,
x
)
)
)
)
)
)
)
)Excel solution 6 for Reverse Only Vowels in Word, proposed by Hussein SATOUR:
=MAP(
A2:A11,
LAMBDA(
x,
IFERROR(
LET(
a,
SEQUENCE(
LEN(
x
)
),
b,
MID(
x,
a,
1
),
c,
IF(
ISNUMBER(
XMATCH(
CODE(
UPPER(
b
)
),
{65,
69,
73,
79,
85}
)
),
a,
0
),
d,
FILTER(
c,
c>0
),
CONCAT(
IFNA(
MID(
x,
XLOOKUP(
c,
d,
INDEX(
d,
COUNT(
d
)+1-SEQUENCE(
COUNT(
d
)
)
)
),
1
),
b
)
)
),
x
)
)
)Excel solution 7 for Reverse Only Vowels in Word, proposed by Sunny Baggu:
=MAP(
A2:A11,
LAMBDA(
x,
LET(
_vow,
{"a"; "e"; "i"; "o"; "u"},
_seq,
SEQUENCE(
LEN(
x
)
),
_m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
_loc,
XMATCH(
_m,
_vow
),
_order,
TOCOL(
IF(
_loc,
_seq,
),
3
),
_revorder,
SORT(
_order,
,
-1
),
_newloc,
IFNA(
XLOOKUP(
_seq,
_order,
_revorder
),
_seq
),
IFERROR(
CONCAT(
INDEX(
_m,
_newloc
)
),
x
)
)
)
)Excel solution 8 for Reverse Only Vowels in Word, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A11,LAMBDA(x,LET(s,SEQUENCE(LEN(x)),m,MID(x,s,1),p,TOCOL(IF(SEARCH(m,"aeiou"),s,),3),IFERROR(REDUCE(x,p,LAMBDA(a,b,REPLACE(a,b,1,INDEX(m,XLOOKUP(b,p,SORT(p,,-1)))))),x))))Excel solution 9 for Reverse Only Vowels in Word, proposed by Abdallah Ally:
=MAP(
A2:A11,
LAMBDA(
s,
LET(
a,
s,
b,
LEN(
a
),
c,
MID(
a,
SEQUENCE(
b
),
1
),
d,
FILTER(
HSTACK(
SEQUENCE(
b
),
c
),
BYROW(
c,
LAMBDA(
x,
OR(
x={"a",
"e",
"i",
"o",
"u"}
)
)
)
),
e,
HSTACK(
d,
SORTBY(
CHOOSECOLS(
d,
2
),
TAKE(
d,
,
1
),
-1
)
),
IFERROR(
REDUCE(
a,
TAKE(
e,
,
1
),
LAMBDA(
u,
v,
REPLACE(
& u,
v,
1,
VLOOKUP(
v,
e,
3,
FALSE
)
)
)
),
a
)
)
)
)Excel solution 10 for Reverse Only Vowels in Word, proposed by Julien Lacaze:
=LET(
data,
A2:A11,
split,
LAMBDA(
text,
seq,
MID(
text,
SEQUENCE(
LEN(
text
),
,
IF(
seq=-1,
LEN(
text
),
1
),
seq
),
1
)
),
isVowel,
LAMBDA(
text,
ISNUMBER(
SEARCH(
text,
"aeiou"
)
)
),
MAP(
data,
LAMBDA(
d,
LET(
sq,
SEQUENCE(
LEN(
d
)
),
sn,
split(
d,
1
),
srev,
split(
d,
-1
),
CONCAT(
IF(
isVowel(
sn
),
XLOOKUP(
sq,
SORTBY(
sq,
isVowel(
sn
),
-1
),
SORTBY(
srev,
isVowel(
srev
),
-1
)
),
sn
)
)
)
)
)
)Excel solution 11 for Reverse Only Vowels in Word, proposed by Mihai Radu O:
= v)))) *
SEQUENCE(
LEN(
a
)
),
d,
FILTER(
b,
c > 0
),
e,
FILTER(
c,
c > 0
),
f,
SORTBY(
d,
e,
-1
),
CONCAT(
IF(
c = 0,
b,
XLOOKUP(
c,
e,
f
)
)
)
)
)
)Excel solution 12 for Reverse Only Vowels in Word, proposed by Giorgi Goderdzishvili:
=MAP(A2:A11,LAMBDA(x,LET(
wrd,x,
chr, MID(wrd, SEQUENCE(,LEN(wrd)),1),
isVow, (chr="a")+(chr="e")+(chr="I")+(chr="o")+(chr="u"),
flt,CONCAT( FILTER(chr,isVow)),
rev, MID(flt, SEQUENCE(,LEN(flt),LEN(flt),-1),1),
sc, SCAN(0,--isVow,LAMBDA(x,y,x+y)),
mp, MAP(isVow,chr,sc, LAMBDA(a,b,c, IF(a,INDEX(rev,1,c),b))),
CONCAT(mp))))Excel solution 13 for Reverse Only Vowels in Word, proposed by Andres Rojas Moncada:
=LET(_sec,SECUENCIA(1,LARGO(B3)),
_car,EXTRAE(B3,_sec,1),
_esvoc,ESNUMERO(COINCIDIRX(_car,{"a","e","i","o","u"})),
_voc,FILTRAR(_car,_esvoc),
_vocinv,ORDENARPOR(_voc,SECUENCIA(1,CONTARA(_voc)),-1),
_ind,SCAN(0,_esvoc,LAMBDA(_acu,_bool,SI(_bool,_acu+1,_acu))),
CONCAT(SI(_esvoc,INDICE(_vocinv,_ind),_car)))Excel solution 14 for Reverse Only Vowels in Word, proposed by Jeff Blakley:
=MAP(A2:A11,
LAMBDA(wd,
LET(seq,
SEQUENCE(
LEN(
wd
)
),
chars,
MID(
wd,
seq,
1
),
pos,
seq*BYROW(
chars={"a",
"e",
"i",
"o",
"u"},
LAMBDA(
rw,
SUM(
--rw
)
)
),
ct,
(pos>0) * SCAN(0,
pos,
LAMBDA(a,
b,
a+(b>0))),
CONCAT(
MAP(
chars,
ct,
LAMBDA(
a,
b,
IF(
b,
INDEX(
chars,
LARGE(
pos,
b
)
),
a
)
)
)
))))Solving the challenge of Reverse Only Vowels in Word with Python in Excel
Python in Excel solution 1 for Reverse Only Vowels in Word, proposed by Bo Rydobon 🇹🇭:
Another Python
import re
v ='aeiouAEIOU'
[''.join([c for c in w if c in v][-len(re.sub(r'[^'+v+']','',w[:i+1]))] if a in v else a for i,a in enumerate(w)) for w in xl("A2:A11")[0]]
Python in Excel solution 2 for Reverse Only Vowels in Word, proposed by Bo Rydobon 🇹🇭:
Python sortby
sortby = lambda a,b: [a[0] for a in sorted(zip(a,b),key=lambda x: x[1])]
[''.join(sortby(sortby(z,[x if y else 99 for x,y in enumerate([s in 'aeiouAEIOU' for s in z])]),
sortby(range(len(z)),[not s in 'aeiouAEIOU' for s in z]))) for z in xl("B2:B11")[0]]Python in Excel solution 3 for Reverse Only Vowels in Word, proposed by Bo Rydobon 🇹🇭:
def sow(z):
w = [s in 'aeiouAEIOU' for s in z]
c = [-x if y else 9 for x,y in zip(np.cumsum(w),w)]
return ''.join(np.array(sorted(zip(sorted(zip(z,c),key=lambda x:x[1]),sorted(enumerate(c),key=lambda x:x[1]>0)),key=lambda x:x[1][0]))[:,0][:,0])
[sow(a) for a in xl("A2:A11")[0]]
Python in Excel solution 4 for Reverse Only Vowels in Word, proposed by 🇰🇷 Taeyong Shin:
Python
def reverse_vowels(word):
return ''.join(vowels.pop() if c.lower() in 'aeiou' else c for c in word)
xl("A2:A11")[0].map(reverse_vowels).tolist()
Python in Excel solution 5 for Reverse Only Vowels in Word, proposed by Diarmuid Early:
Python solution
def revVow(input):
vowRev = [ltr for ltr in input if ltr.lower() in "aeiou"][::-1]
sepVow = sorted(enumerate(input),key=lambda a:a[1].lower() not in "aeiou")
vowInOrd = [[x[1][0], vowRev[x[0]] if x[0] < len(vowRev) else x[1][1]] for x in enumerate(sepVow)]
return "".join([c[1] for c in sorted(vowInOrd)])
list(map(revVow,xl("A2:A11")[0]))
I'm saving all my Python solutions to these challenges here if anyone wants to explore:
bit.ly/PythonLearningFolder
Solving the challenge of Reverse Only Vowels in Word with R
R solution 1 for Reverse Only Vowels in Word, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Vowels Reverse.xlsx")
reverse_vowels = function(word) {
vowels = c("a","e","i","o","u","A","E","I","O","U")
rev_vowels_positions = rev(vowels_positions)
splitted_word[vowels_positions] <- splitted_word[rev_vowels_positions]
return(rev_word)
}
reverse_vowels(a)
result = input %>%
mutate(my_answer = map_chr(Words,reverse_vowels),
test = Answer == my_answer)
&&