Swap the first and last letter of each words in given names. But if either first or last letter or both are vowels, then don’t swap for that word. Ex. Barack Obama In first word, B and k will be swapped. In second word, O and a both are vowels, hence swapping will not be done for this word. Hence, answer would be Karacb Obama.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 317
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Swap first and last letters unless vowels with Power Query
Power Query solution 1 for Swap first and last letters unless vowels, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.Split([Names], " "),
each
let
c = each Text.PositionOf("bcdfghjklmnpqrstvwxyz", _, 0, Comparer.OrdinalIgnoreCase) >= 0,
f = Text.At(_, 0),
l = Text.End(_, 1)
in
if c(f) and c(l) then Text.Proper(l & Text.Middle(_, 1, Text.Length(_) - 2) & f) else _
),
" "
)
)
in
SPower Query solution 2 for Swap first and last letters unless vowels, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Names], " "),
b = List.Transform(a, Text.Lower),
c = Text.Combine(
List.Transform(
b,
each
if List.ContainsAny({"a", "e", "i", "o", "u"}, {Text.Start(_, 1)} & {Text.End(_, 1)})
or Text.Length(_)
< 3
then
Text.Proper(_)
else
Text.Proper(
Text.End(_, 1)
& Text.Combine(List.RemoveLastN(List.Skip(Text.ToList(_))))
& Text.Start(_, 1)
)
),
" "
)
in
c
)[[Answer]]
in
SolPower Query solution 3 for Swap first and last letters unless vowels, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Transform(
Text.Split([Names], " "),
each {_}
& {
List.ContainsAny(
Text.ToList(Text.Lower(Text.Start(_, 1) & Text.End(_, 1))),
{"a", "e", "i", "o", "u"}
)
}
),
b = Text.Combine(
List.Transform(
a,
(x) =>
if x{1} = false and not Text.Contains(x{0}, ".") then
Text.Proper(
Text.End(x{0}, 1)
& Text.RemoveRange(Text.RemoveRange(x{0}, 0), Text.Length(x{0}) - 2)
& Text.Start(x{0}, 1)
)
else
x{0}
),
" "
)
][b]
)
in
resPower Query solution 4 for Swap first and last letters unless vowels, proposed by Luke Jarych:
let
Source = Table1,
vowels = {"a", "e", "i", "o", "u"},
AddAnswerColumn = Table.AddColumn(
Source,
"Expected Answer",
each
let
a = Text.Split(Text.Lower([Names]), " "),
b = List.Transform(
a,
each
if List.ContainsAny(vowels, {Text.Start(_, 1)} & {Text.End(_, 1)}) then
Text.Proper(_)
else if Text.Length(_) < 3 then
Text.Proper(_)
else
Text.Proper(Text.End(_, 1) & Text.Range(_, 1, Text.Length(_) - 2) & Text.Start(_, 1))
),
c = Text.Combine(b, " ")
in
c
)[[Expected Answer]]
in
AddAnswerColumnSolving the challenge of Swap first and last letters unless vowels with Excel
Excel solution 1 for Swap first and last letters unless vowels, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
PROPER(
TEXTJOIN(
" ",
,
MAP(
TEXTSPLIT(
a,
" "
),
LAMBDA(
b,
LET(
l,
LEFT(
b
),
r,
RIGHT(
b
),
IF(
COUNT(
SEARCH(
{"a",
"e",
"i",
"o",
"u",
"."},
l&r
)
),
b,
r&MID(
b,
2,
LEN(
b
)-2
)&l
)
)
)
)
)
)
)
)Excel solution 2 for Swap first and last letters unless vowels, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
PROPER(
TRIM(
REDUCE(
"",
TEXTSPLIT(
x,
" "
),
LAMBDA(
a,
t,
LET(
r,
RIGHT(
t
),
l,
LEFT(
t
),
v,
"aeiou.",
a&" "&IF(
ISERR(
SEARCH(
l,
v
)
)+ISERR(
SEARCH(
r,
v
)
)=2,
r&MID(
t,
2,
LEN(
t
)-2
)&l,
t
)
)
)
)
)
)
)
)Excel solution 3 for Swap first and last letters unless vowels, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
n,
TEXTJOIN(
" ",
,
MAP(
TEXTSPLIT(
n,
" "
),
LAMBDA(
x,
LET(
l,
LEFT(
x
),
r,
RIGHT(
x
),
IF(
COUNT(
SEARCH(
HSTACK(
l,
r
),
"aeiou."
)
),
x,
PROPER(
r&MID(
x,
2,
LEN(
x
)-2
)&l
)
)
)
)
)
)
)
)Excel solution 4 for Swap first and last letters unless vowels, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(a,PROPER(TEXTJOIN(" ",,
MAP(TEXTSPLIT(a," "),LAMBDA(d,
LET(e,LEFT(d),r,RIGHT(d),IF(COUNT(SEARCH(
{".","A","E","I","O","U"},e&r)),d,r&MID(d,2,LEN(d)-2)&e))))))))Excel solution 5 for Swap first and last letters unless vowels, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
TEXTSPLIT(
x,
,
" "
),
b,
LEFT(
a
),
c,
RIGHT(
a
),
d,
{"a",
"e",
"i",
"o",
"u",
"."},
TEXTJOIN(" ",
,
PROPER(IF(MMULT((d=c)+(d=b),
{1;1;1;1;1;1}),
a,
c&MID(
a,
2,
LEN(
a
)-2
)&b))))))Excel solution 6 for Swap first and last letters unless vowels, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
TEXTSPLIT(
x,
,
" "
),
b,
LEFT(
a
),
c,
RIGHT(
a
),
d,
"aeiou.",
TEXTJOIN(" ",
,
PROPER(IF((IFERROR(
SEARCH(
b,
d
),
0
))+(IFERROR(
SEARCH(
c,
d
),
0
))>0,
a,
c&MID(
a,
2,
LEN(
a
)-2
)&b))))))Excel solution 7 for Swap first and last letters unless vowels, proposed by Julian Poeltl:
=MAP(A2:A10,
LAMBDA(N,
LET(S,
TEXTSPLIT(
N,
" "
),
TEXTJOIN(" ",
,
PROPER(MAP(S,
LAMBDA(A,
LET(R,
RIGHT(
A
),
L,
LEFT(
A
),
IF((LEN(
A
)>2)*(NOT(
ISNUMBER(
SEARCH(
L,
"AEIOU"
)
)
))*(NOT(
ISNUMBER(
SEARCH(
R,
"aeiou"
)
)
)),
R&MID(
A,
2,
LEN(
A
)-2
)&L,
A)))))))))Excel solution 8 for Swap first and last letters unless vowels, proposed by Timothée BLIOT:
=MAP(A2:A10,
LAMBDA(z,
LET(A,
TEXTSPLIT(
z,
" "
),
B,
LAMBDA(
n,
SUM(
--ISNUMBER(
SEARCH(
TEXTSPLIT(
"a:e:i:o:u",
":"
),
n
)
)
)
),
TEXTJOIN(" ",
,
MAP(A,
LAMBDA(x,
LET(L,
LEFT(
x
),
R,
RIGHT(
x
),
IF((B(
L
)+B(
R
)+--(R=".")>0),
x,
PROPER(
R&MID(
x,
2,
LEN(
x
)-2
)&L
)))))))))Excel solution 9 for Swap first and last letters unless vowels, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
TEXTJOIN(
" ",
,
PROPER(
MAP(
TEXTSPLIT(
UPPER(
x
),
" "
),
LAMBDA(
y,
LET(
a,
LEFT(
y
),
b,
RIGHT(
y
),
c,
"AEIOU",
IF(
IFERROR(
FIND(
a,
c
),
0
) + IFERROR(
FIND(
b,
c
),
0
) >0,
y,
IF(
AND(
AND(
CODE(
a
)>64,
CODE(
a
)<91
),
AND(
CODE(
b
)>64,
CODE(
b
)<91
)
),
b & MID(
y,
2,
LEN(
y
)-2
) & a,
y
)
)
)
)
)
)
)
)
)Excel solution 10 for Swap first and last letters unless vowels, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
_ts,
TEXTSPLIT(
x,
" "
),
_e1,
LAMBDA(
a,
RIGHT(
a
) & MID(
a,
2,
LEN(
a
) - 2
) & LEFT(
a
)
),
_cond,
BYCOL(
VSTACK(
LEFT(
_ts
),
RIGHT(
_ts
)
),
LAMBDA(
x,
OR(
MAP(
x,
LAMBDA(
a,
OR(
a = {"a&"; "e"; "i"; "o"; "u"},
a = "."
)
)
)
)
)
),
TEXTJOIN(
" ",
,
PROPER(
IF(
_cond,
_ts,
_e1(
_ts
)
)
)
)
)
)
)Excel solution 11 for Swap first and last letters unless vowels, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
u,
LET(
a,
TEXTSPLIT(
u,
" "
),
b,
{"a",
"e",
"i",
"o",
"u",
"."},
TRIM(
PROPER(
REDUCE(
"",
a,
LAMBDA(
x,
y,
IF(
OR(
LEFT(
y
)=b
)+OR(
RIGHT(
y
)=b
),
CONCAT(
x,
" ",
y
),
CONCAT(
x,
" ",
RIGHT(
y
),
MID(
y,
2,
LEN(
y
)-2
),
LEFT(
y
)
)
)
)
)
)
)
)
)
)Excel solution 12 for Swap first and last letters unless vowels, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(A2:A10, LAMBDA(a, TEXTJOIN(" ",,MAP(TEXTSPLIT(a,," "), LAMBDA(r, LET(v,{"a";"e";"i";"o";"u"},f,LEFT(r),l,RIGHT(r),s,LEN(r), IF(OR(f=v,l=v,s=2), r,UPPER(l)& MID(r,2,s-2 )&LOWER(f))))))))Excel solution 13 for Swap first and last letters unless vowels, proposed by Md. Zohurul Islam:
=MAP(
A2:A10,
LAMBDA(
y,
LET(
s,
TEXTSPLIT(
y,
,
" "
),
u,
MAP(
s,
LAMBDA(
x,
LET(
a,
LEFT(
x
),
b,
RIGHT(
x
),
n,
SUM(
ABS(
ISERROR(
SEARCH(
HSTACK(
a,
b
),
"aeiou"
)
)
)
),
c,
IFERROR(
CONCAT(
b,
CONCAT(
DROP(
DROP(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
1
),
-1
)
),
a
),
x
),
d,
PROPER(
IF(
n>1,
c,
x
)
),
d
)
)
),
v,
TEXTJOIN(
" ",
,
u
),
v
)
)
)Excel solution 14 for Swap first and last letters unless vowels, proposed by Pieter de Bruijn:
=LET(
t,
TEXTSPLIT(
TEXTAFTER(
" "&A2:A10&" |",
" ",
SEQUENCE(
,
5
)
),
" "
),
l,
LEFT(
t
),
r,
RIGHT(
t
),
PROPER(
DROP(
TRIM(
TEXTSPLIT(
CONCAT(
IFNA(
IF(
LEN(
t
)>2,
IF(
WRAPROWS(
MMULT(
--ISNUMBER(
SEARCH(
HSTACK(
TOCOL(
l
),
TOCOL(
r
)
),
"aeiou"
)
),
{1;1}
),
5
),
t&" ",
r&MID(
t,
2,
LEN(
t
)-2
)&l&" "
),
t&" "
),
""
)
),
,
"|"
)
),
-1
)
)
)
or borrowing the IF(ISERR(
LEFT / RIGHT
) logic from Rick Rothstein:
=LET(
t,
TEXTSPLIT(
TEXTAFTER(
" "&A2:A10&" |",
" ",
SEQUENCE(
,
5
)
),
" "
),
l,
LEFT(
t
),
r,
RIGHT(
t
),
PROPER(
DROP(
TRIM(
TEXTSPLIT(
CONCAT(
IFNA(
IF(
LEN(
t
)>2,
IF(
ISERR(
SEARCH(
l,
"aeiou"
)
)+ISERR(
SEARCH(
r,
"aeiou"
)
)<2,
t&" ",
r&MID(
t,
2,
LEN(
t
)-2
)&l&" "
),
t&" "
),
""
)
),
,
"|"
)
),
-1
)
)
)Excel solution 15 for Swap first and last letters unless vowels, proposed by Giorgi Goderdzishvili:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
nm,
x,
vw,
"aeiou",
wrd,
TEXTSPLIT(
nm,
" "
),
fr,
LEFT(
TEXTSPLIT(
wrd,
" "
),
1
),
lst,
RIGHT(
TEXTSPLIT(
wrd,
" "
),
1
),
fr_vow,
ISNUMBER(
SEARCH(
fr,
vw
)
),
sc_vow,
ISNUMBER(
SEARCH(
lst,
vw
)
),
lgc,
IF(
lst=".",
wrd,
IF(
fr_vow+sc_vow,
wrd,
REPLACE(
REPLACE(
wrd,
1,
1,
lst
),
LEN(
wrd
),
1,
fr
)
)
),
TEXTJOIN(
" ",
,
lgc
)
)
)
)Excel solution 16 for Swap first and last letters unless vowels, proposed by Edwin Tisnado:
=MAP(
A2:A10,
LAMBDA(
a,
TEXTJOIN(
" ",
,
MAP(
TEXTSPLIT(
a,
" "
),
LAMBDA(
t,
LET(
x,
LEFT(
t
),
y,
RIGHT(
t
),
z,
"aeiou.",
IF(
ISERROR(
SEARCH(
x,
z
)
)=ISERROR(
SEARCH(
y,
z
)
),
PROPER(
REPLACE(
REPLACE(
t,
1,
1,
y
),
LEN(
t
),
1,
x
)
),
t
)
)
)
)
)
)
)Excel solution 17 for Swap first and last letters unless vowels, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,
LAMBDA(a,
LET(k,
TEXTSPLIT(
a,
" "
),
b,
(LEFT(
k
)&RIGHT(
k
)),
c,
BYCOL(
b,
LAMBDA(
x,
SUM(
--ISERR(
SEARCH(
{"a";"e";"o";"i";"u";"."},
x
)
)
)
)
)=5,
TRIM(
CONCAT(
PROPER(
MAP(
c,
b,
k,
LAMBDA(
x,
y,
z,
IF(
x,
" "&z,
" "&RIGHT(
y
)&MID(
z,
2,
LEN(
z
)-2
)&LEFT(
y
)
)
)
)
)
)
))))Excel solution 18 for Swap first and last letters unless vowels, proposed by Daniel Garzia:
=MAP(
A2:A10,
LAMBDA(
x,
PROPER(
TEXTJOIN(
" ",
,
MAP(
TEXTSPLIT(
x,
,
" "
),
LAMBDA(
r,
LET(
f,
LEFT(
r
),
l,
RIGHT(
r
),
IF(
AND(
ISERR(
SEARCH(
VSTACK(
f,
l
),
"aeiou."
)
)
),
l&MID(
r,
2,
LEN(
r
)-2
)&f,
r
)
)
)
)
)
)
)
)Excel solution 19 for Swap first and last letters unless vowels, proposed by Rayan S.:
=MAP(
A2:A10,
LAMBDA(
arr,
LET(
v,
{"a",
"e",
"i",
"o",
"u"},
s,
TEXTSPLIT(
arr,
" "
),
mid,
MID(
s,
2,
LEN(
s
)-2
),
l,
LEFT(
s,
1
),
r,
RIGHT(
s,
1
),
a,
UPPER(
r
)&mid&LOWER(
l
),
TEXTJOIN(
" ",
,
IF(
LEN(
s
)<3,
s,
IF(
IFNA(
IFNA(
MATCH(
r,
v,
0
),
MATCH(
l,
v,
0
)
),
0
)>0,
s,
a
)
)
& )
)
)
)Excel solution 20 for Swap first and last letters unless vowels, proposed by Hazem Hassan:
=MAP(A2:A10,
LAMBDA(y,
LET(a,
{"a",
"e",
"I",
"o",
"u",
"."},
b,
TEXTSPLIT(
y,
,
" "
),
c,
RIGHT(
b
),
d,
LEFT(
b
),
CONCAT(PROPER(IF(BYROW((c=a)+(d=a),
LAMBDA(
x,
SUM(
x
)
))>=1,
b,
c&MID(
b,
2,
LEN(
b
)-2
)&d))&" "))))Solving the challenge of Swap first and last letters unless vowels with Python
Python solution 1 for Swap first and last letters unless vowels, proposed by Luke Jarych:
Solving the challenge of Swap first and last letters unless vowels with Python in Excel
Python in Excel solution 1 for Swap first and last letters unless vowels, proposed by John V.:
Hi everyone!
One [Python] option could be:
def f(s):
return ' '.join(w if re.search('[aeiou.]', w[0] + w[-1], re.I) else w[-1] + w[1:-1] + w[0] for w in s.split()).title()
[f(s) for s in xl("A2:A10")[0]]
Blessings!
Python in Excel solution 2 for Swap first and last letters unless vowels, proposed by JvdV -:
import re
[re.sub(r'(?i)b([^aeiouW])(w*)([^aeiouW])b', r'321', s).title() for s in xl("A2:A10")[0]]
xl("A2:A10")[0].replace(r'(?i)b([^aeiouW])(w*)([^aeiouW])b', r'321', regex=True).str.title().values
Solving the challenge of Swap first and last letters unless vowels with R
R solution 1 for Swap first and last letters unless vowels, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
switch_consonants <- function(word) {
vowels <- c("a", "e", "i", "o", "u", "A", "E", "I", "O", "U")
if (str_detect(w, "^\w\.$")) {
return(w)
}
first_char <- str_sub(w, 1, 1)
last_char <- str_sub(w, nchar(w), nchar(w))
if (!(first_char %in% vowels) && !(last_char %in% vowels)) {
w <- paste0(last_char, str_sub(w, 2, nchar(w)-1), first_char)
}
return(w)
})
paste(collapse = " ") %>%
str_to_lower() %>%
str_to_title()
return(result)
}
result = input %>%
mutate(`Answer Expected` = map_chr(Names, switch_consonants)) %>%
select(-Names)
identical(result, test)
#> [1] TRUE
R solution 2 for Swap first and last letters unless vowels, proposed by Krzysztof Nowak:
unnest_tokens(input = Names,output = Seperated,token = "regex",pattern = "\s",to_lower = F,drop = F) |>
mutate(
FirstAndLast = paste(substr(Seperated ,1,1),substr(Seperated ,nchar(Seperated),nchar(Seperated)),sep = ""),
CountVowel = str_count(FirstAndLast, "(?i)[aeiou]|S."),
replaced = case_when(CountVowel == 0 ~
paste0(substring(Seperated, nchar(Seperated), nchar(Seperated)), substring(Seperated, 2, nchar(Seperated) - 1), substring(Seperated, 1, 1)),
.default = Seperated),
replaced = toTitleCase(tolower(replaced))) |>
summarize(replaced = paste(replaced, collapse = " "),.by = Names) |>
ungroup() |>
bind_cols(Expected) |>
mutate(test = replaced == Expected)
Answer
Solving the challenge of Swap first and last letters unless vowels with Excel VBA
Excel VBA solution 1 for Swap first and last letters unless vowels, proposed by Nicolas Micot:
VBA solution:
Function f_echangeExtremites(ByVal texte As String) As String
Dim mots() As String, firstLetter As String, lastLetter As String
mots = Split(texte, " ")
For i = 0 To UBound(mots, 1)
firstLetter = LCase(Left(mots(i), 1))
lastLetter = LCase(Right(mots(i), 1))
If firstLetter Like "[!aeiuoy]" And firstLetter Like "[a-z]" _
And lastLetter Like "[!aeiuoy]" And lastLetter Like "[a-z]" Then
mots(i) = UCase(lastLetter) & IIf(Len(mots(i)) > 2, Mid(mots(i), 2, Len(mots(i)) - 2), "") & LCase(firstLetter)
End If
Next i
f_echangeExtremites = Join(mots, " ")
End Function
Excel VBA solution 2 for Swap first and last letters unless vowels, proposed by Nicolas Micot:
Function f_Like(ByVal tableau As Variant, ByVal comparaison As String, Optional caseSensible As Boolean) As Boolean()
Dim dimension As Integer
Dim test
Dim tabComparaison() As Boolean
If IsMissing(caseSensible) Then caseSensible = False
test = 0
On Error Resume Next
tableau = tableau.Value
test = UBound(tableau, 2)
On Error GoTo 0
If test > 0 Then
dimension = 2
Else
dimension = 1
End If
If dimension = 1 Then
ReDim tabComparaison(LBound(tableau, 1) To UBound(tableau, 1))
For i = LBound(tableau, 1) To UBound(tableau, 1)
tabComparaison(i) = IIf(Not caseSensible, LCase(tableau(i)), tableau(i)) Like comparaison
Next i
Else
ReDim tabComparaison(LBound(tableau, 1) To UBound(tableau, 1), LBound(tableau, 2) To UBound(tableau, 2))
For i = LBound(tableau, 1) To UBound(tableau, 1)
For j = LBound(tableau, 2) To UBound(tableau, 2)
tabComparaison(i, j) = IIf(Not caseSensible, LCase(tableau(i, j)), tableau(i, j)) Like comparaison
Next j
Next i
End If
f_Like = tabComparaison
End Function
Solving the challenge of Swap first and last letters unless vowels with DAX
DAX solution 1 for Swap first and last letters unless vowels, proposed by Zoran Milokanović:
Answer Expected =
VAR n = SUBSTITUTE(Input[Names], " ", "|")
VAR t = ADDCOLUMNS(GENERATESERIES(1, PATHLENGTH(n)), "Word",
VAR w = PATHITEM(n, [Value])
VAR f = LOWER(LEFT(w, 1))
VAR l = UPPER(RIGHT(w, 1))
RETURN SWITCH(TRUE, IF(UNICODE(f) >= 97 && UNICODE(f) <= 122 && NOT(UNICODE(f) IN {97, 101, 105, 111, 117}), TRUE, FALSE) && IF(UNICODE(l) >= 65 && UNICODE(l) <= 90 && NOT(UNICODE(l) IN {65, 69, 73, 79, 85}), TRUE, FALSE), l & MID(w, 2, LEN(w) - 2) & f, w)
)
RETURN CONCATENATEX(t, [Word], " ")
&