Locate the vowels which are between two consonants. List thus found vowels along with its left and right consonants. Ex. elephant => lep, han ceiling => ceil, lin
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 560
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Vowels Between Consonants with Power Query
Power Query solution 1 for Find Vowels Between Consonants, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
a = [Words],
b = Text.Length(a) - 3,
c = List.RemoveNulls(
List.TransformMany(
{0 .. b},
(x) => {0 .. b},
(x, y) => try Text.Range(a, x, y + 3) otherwise null
)
),
d = {"a" .. "z"},
e = {"a", "e", "i", "o", "u"},
f = List.Select(
c,
each List.Contains(List.Difference(d, e), Text.Start(_, 1))
and List.Contains(List.Difference(d, e), Text.End(_, 1))
and List.ContainsAll(e, List.RemoveLastN(List.RemoveFirstN(Text.ToList(_))))
),
g = Text.Combine(f, ", ")
in
g
)
in
SolPower Query solution 2 for Find Vowels Between Consonants, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
E = Text.EndsWith,
a = Text.ToList(x),
b = List.Difference({"a" .. "z"}, {"a", "e", "i", "o", "u"}),
c = Text.Combine(
List.Transform(
List.RemoveLastN(List.RemoveFirstN(a)),
each if _ = "a" or _ = "e" or _ = "i" or _ = "o" or _ = "u" then _ else _ & _
)
),
d = List.First(a) & c & List.Last(a),
e = Splitter.SplitTextByCharacterTransition(b, b)(d),
f = if List.Count(e) = 1 then {null} else e,
g = List.Select(
f,
each Text.Length(_)
>= 3
and not E(_, "a")
and not E(_, "e")
and not E(_, "i")
and not E(_, "o")
and not E(_, "u")
)
in
Text.Combine(g, ", "),
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Words]))
in
SolPower Query solution 3 for Find Vowels Between Consonants, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
SolutionCol = Table.AddColumn(
Source,
"Answer Expected",
each [
step1 = Text.ToList([Words]),
step2 = Text.PositionOfAny([Words], {"a", "e", "i", "o", "u"}, Occurrence.All),
step3 = List.Count(step1) - 1,
step4 = List.Select(step2, (x) => x > 0 and x < step3),
step5 = List.Select(
step4,
(x) => (
Text.Contains("aeiou", Text.At([Words], x - 1))
or Text.Contains("aeiou", Text.At([Words], x + 1))
)
),
step6 =
if (
(List.Min(step2) = 0 and List.Min(step5) = 1)
or (List.Max(step2) = step3 and List.Max(step5) = step3 - 1)
)
then
List.Transform(step2, (x) => null)
else
step5,
step7 = List.Select(
step4,
(x) =>
not (
Text.Contains("aeiou", Text.At([Words], x - 1))
or Text.Contains("aeiou", Text.At([Words], x + 1))
)
),
step8 = {
try
Text.Combine(
List.Transform({List.Min(step6) - 1 .. List.Max(step6) + 1}, (x) => step1{x}),
""
)
otherwise
null
},
step9 = List.Transform(step7, (x) => Text.Combine({step1{x - 1}, step1{x}, step1{x + 1}}, "")),
result = Text.Combine(step9 & step8, ", ")
][result]
)
in
SolutionColSolving the challenge of Find Vowels Between Consonants with Excel
Excel solution 1 for Find Vowels Between Consonants, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
TEXTJOIN(
", ",
,
IFNA(
REGEXEXTRACT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
99
),
"^([^aeiou])[aeiou]+((?1))"
),
""
)
)
)
)Excel solution 2 for Find Vowels Between Consonants, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),m,MID(CONCAT(--ISERR(FIND(MID(a,s,1),"aeiou"))),s,99),TEXTJOIN(", ",,IFERROR(MID(a,s,(LEFT(m,2)="10")*FIND(1,m,2)),"")))))Excel solution 3 for Find Vowels Between Consonants, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
w,
LET(
c,
TEXTSPLIT(
w,
,
{"a",
"e",
"i",
"o",
"u"},
1
),
IFERROR(
MID(
REDUCE(
"",
SEQUENCE(
ROWS(
c
)-1
),
LAMBDA(
a,
x,
a&", "&RIGHT(
INDEX(
c,
x
)
)&INDEX(
TEXTSPLIT(
w,
,
c
),
x+1
)&LEFT(
INDEX(
c,
x+1
)
)
)
),
3,
99
),
""
)
)
)
)
Note: This is a modification of my original formula (now removed)Excel solution 4 for Find Vowels Between Consonants, proposed by John V.:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
w,
LET(
d,
TEXTSPLIT,
i,
INDEX,
c,
d(
w,
,
{"a";"e";"i";"o";"u"},
1
),
ARRAYTOTEXT(
MAP(
SEQUENCE(
ROWS(
c
)-1
),
LAMBDA(
x,
RIGHT(
i(
c,
x
)
)&i(
d(
w,
,
c
),
1+x
)&LEFT(
i(
c,
1+x
)
)
)
)
)
)
)
),
""
)Excel solution 5 for Find Vowels Between Consonants, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(z,
LET(a,
CHAR(
SEQUENCE(
26,
,
97
)
),
b,
{"a",
"e",
"i",
"o",
"u"},
p,
{1;1;1;1;1},
c,
TOCOL(IFS(MMULT(-(a=b),
p)+1,
a),
3),
d,
TEXTSPLIT(
z,
,
c,
1
),
e,
MAP(
SEQUENCE(
ROWS(
d
)
),
LAMBDA(
v,
SUM(
N(
CHOOSEROWS(
d,
v
)=TAKE(
d,
v
)
)
)
)
),
f,
MAP(
d,
e,
LAMBDA(
x,
y,
RIGHT(
TEXTBEFORE(
z,
x,
y
)
)
)
),
g,
MAP(
d,
e,
LAMBDA(
x,
y,
LEFT(
TEXTAFTER(
z,
x,
y
)
)
)
),
h,
f&d&g,
TEXTJOIN(
", ",
,
IF(
MMULT(
N(
LEFT(
h
)=b
),
p
)+MMULT(
N(
RIGHT(
h
)=b
),
p
),
"",
h
)
))))Excel solution 6 for Find Vowels Between Consonants, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(W,LET(SL,SEQUENCE(LEN(W)),S,MID(W,SL,1),V,--ISNUMBER(SEARCH(S,"aeiou")),VV,VSTACK(IF(TAKE(V,1)=1,2,0),DROP(DROP(V,1),-1),IF(TAKE(V,-1)=1,2,0)),C,SCAN(0,DROP((VV=1)*(VSTACK(1,VV)=0),-1),SUM),LET(R,MAP(UNIQUE(DROP(C,1)),LAMBDA(A,TAKE(FILTER(SL,(C=A)*(VV=1)),1))),F,FILTER(R,NOT(ISERR(R))),TEXTJOIN(", ",,IFNA(MID(W,F-1,MAP(F,LAMBDA(A,XMATCH(0,DROP(VV,A))))+2),""))))))Excel solution 7 for Find Vowels Between Consonants, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
LAMBDA(a,
LET(
vowl,
"aeiou",
splt,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
rc,
SCAN(
0,
splt,
LAMBDA(
a,
b,
IF(
ISERR(
FIND(
b,
vowl
)
),
a + 1,
a
)
)
),
grp,
GROUPBY(
rc,
splt,
CONCAT,
0,
0
),
grprc,
TAKE(
grp,
,
1
),
grpw,
TAKE(
grp,
,
-1
),
fltr,
FILTER(grp,
ISNUMBER(
FIND(
RIGHT(
grpw
),
vowl
)
) * (LEN(
grpw
) <> 1)),
fltrrc,
TAKE(
fltr,
,
1
),
fltrw,
TAKE(
fltr,
,
-1
),
final,
MAP(
fltrrc,
fltrw,
LAMBDA(
a,
b,
b & LEFT(
XLOOKUP(
a + 1,
grprc,
grpw
)
)
)
),
rtrn,
IFERROR(
ARRAYTOTEXT(
TOCOL(
final,
2
)
),
""
),
rtrn
)
)
)Excel solution 8 for Find Vowels Between Consonants, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
TEXTJOIN(
", ",
,
UNIQUE(
TEXTSPLIT(
TEXTJOIN(
", ",
0,
MAP(
MID(
z,
SEQUENCE(
LEN(
z
)
),
LEN(
z
)-SEQUENCE(
LEN(
z
)
)+1
),
LAMBDA(
x,
ARRAYTOTEXT(
IFNA(
REGEXEXTRACT(
x,
"(?<=[^aeiou]?)[^aeiou]{1}[aeiou]+[^aeiou]{1}(?=[^aeiou]?)",
1
),
""
)
)
)
)
),
,
", ",
)
)
)
)
)Excel solution 9 for Find Vowels Between Consonants, proposed by Hussein SATOUR:
=MAP(A2:A10,LAMBDA(x,IFERROR(ARRAYTOTEXT(TOCOL(UNIQUE(REGEXEXTRACT(MID(x,SEQUENCE(LEN(x)-2),99),"[^aeiou]{1}+[aeiou]+[^aeiou]")),3)),"")))Excel solution 10 for Find Vowels Between Consonants, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
LAMBDA(
r,
LET(
s,
SEQUENCE(
LEN(
r
)
),
f,
MAP(
MID(
r,
s,
TOROW(
s
)
),
LAMBDA(
f,
IF(
AND(
LEN(
f
)>2,
REGEXTEST(
f,
"^[^aeious][aeiou]*[^aeious]$",
1
)
),
f,
""
)
)
),
TEXTJOIN(
", ",
1,
UNIQUE(
TOCOL(
f,
3
)
)
)
)
)
)Excel solution 11 for Find Vowels Between Consonants, proposed by JvdV -:
=REGEXREPLACE(
A2:A10,
"(?=([^aeiou]([aeiou]++.))(.*(?2))?)|.",
"$1${3:+, }"
)Excel solution 12 for Find Vowels Between Consonants, proposed by Ziad A.:
=JOIN(", ",SPLIT(REGEXREPLACE(REGEXREPLACE(A2,"[^aeiou]","$0$0"),"([^aeiou][aeiou]+?[^aeiou])|.","$1 ")," "))
Slightly shorter if we allow space instead of comma separation
=TRIM(REGEXREPLACE(REGEXREPLACE(A2,"[^aeiou]","$0$0"),"([^aeiou][aeiou]+?[^aeiou])|.","$1 "))Excel solution 13 for Find Vowels Between Consonan&ts, proposed by Md. Shah Alam, Microsoft Certified Trainer:
=MAP(A2:A10,LAMBDA(x,LET(a,SEQUENCE(LEN(x)),b,CONCAT(--ISERR(SEARCH(MID(x,a,1),"aeiou"))),c,DROP(UNIQUE(SEARCH(10,b,a)),-1),IFERROR(TEXTJOIN(", ",,MID(x,c,IFERROR(SEARCH(1,b,c+1)-c+1,0))),""))))Solving the challenge of Find Vowels Between Consonants with Python
Python solution 1 for Find Vowels Between Consonants, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "560 Vowels between Consonants.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10).fillna({'Answer Expected': ''})
def extract_cvc_overlap(input_string):
return ', '.join(re.findall(r'(?=([^aeiou][aeiou]+[^aeiou]))', input_string)).strip()
input['result'] = input['Words'].apply(extract_cvc_overlap)
print(input['result'].equals(test['Answer Expected'])) # True
Solving the challenge of Find Vowels Between Consonants with Python in Excel
Python in Excel solution 1 for Find Vowels Between Consonants, proposed by Alejandro Campos:
import re
def find_vowels_between_consonants(word):
valid_substrings = []
for start in range(len(word)):
for end in range(start + 2, len(word)):
substring = word[start:end+1]
if re.match(r'^[^aeious][aeiou]+[^aeious]$', substring):
valid_substrings.append(substring)
return ', '.join(pd.unique(valid_substrings))
df = pd.DataFrame({
'Word': words,
'Vowels_Between_Consonants': results
})
df
Solving the challenge of Find Vowels Between Consonants with R
R solution 1 for Find Vowels Between Consonants, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/560 Vowels between Consonants.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10") %>% replace_na(list(`Answer Expected` = ""))
extract_cvc_overlap <- function(input_string) {
pattern <- "(?=([^aeiou][aeiou]+[^aeiou]))"
str_match_all(input_string, pattern) %>%
map_chr(~ paste(.[, 2], collapse = ", ")) %>%
str_trim()
}
result = input %>%
mutate(result = map_chr(Words, extract_cvc_overlap))
all.equal(result$result, test$`Answer Expected`, check.attributes = FALSE)
#> [1] TRUE
&&