Provide a formula to reverse the case of English alphabets.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 36
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Toggle Alphabet Case with Power Query
Power Query solution 1 for Toggle Alphabet Case, proposed by Brian Julius:
let
Source = Table.AddIndexColumn(TextCaseRaw, "Index", 1, 1),
Letters = Table.AddColumn(Source, "Letters", each Text.ToList([String])),
Expand = Table.ExpandListColumn(Letters, "Letters"),
SwapCase = Table.RemoveColumns(
Table.AddColumn(
Expand,
"ChangeCase",
each
if [Letters] = Text.Upper([Letters]) then
Text.Lower([Letters])
else if [Letters] = Text.Lower([Letters]) then
Text.Upper([Letters])
else
[Letters]
),
{"String", "Letters"}
),
Group = Table.Group(
SwapCase,
{"Index"},
{
{
"All",
each _,
type table [String = nullable text, Index = number, Letters = text, ChangeCase = text]
}
}
),
Combine = Table.RemoveColumns(
Table.AddColumn(Group, "Expected Answer", each Text.Combine([All][ChangeCase])),
{"Index", "All"}
)
in
CombinePower Query solution 2 for Toggle Alphabet Case, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CaseSwitch"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Shifted Alphabet",
each Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([String]),
List.Zip({{"a" .. "z", "A" .. "Z"}, {"A" .. "Z", "a" .. "z"}})
),
""
)
)[[Shifted Alphabet]]
in
#"Added Custom"Power Query solution 3 for Toggle Alphabet Case, proposed by Antriksh Sharma:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"JYw9C8IwFEX/yiXgViRKs3RS0MFawUHoEDrEJMrDfGiig//e8hzucA6Hq7XYWi+mRosjpTtMcnh9vE+sziXf6I2VlAuw2CMaCh38lTbzljZH9r2xjwb9YRgYT3aXkwmuMqkW31KRg2O8UPSgCtl2SuH5PxhzCQ6jKVinOZt+",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [String = _t]
),
ChangedType = Table.TransformColumnTypes(Source, {{"String", type text}}),
AddedCustom = Table.AddColumn(
ChangedType,
"Custom",
each Text.Combine(
List.Transform(
Text.ToList([String]),
(Letter) => if Letter = Text.Upper(Letter) then Text.Lower(Letter) else Text.Upper(Letter)
)
)
)
in
AddedCustomPower Query solution 4 for Toggle Alphabet Case, proposed by Excel BI:
Terrific use of List.Zip in combination with List.ReplaceMatchingItems.....Power Query solution 6 for Toggle Alphabet Case, proposed by Melissa de Korte:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Result",
each Text.Combine(
List.Transform(
Text.ToList([String]),
(t) => if List.Contains({"a" .. "z"}, t) then Text.Upper(t) else Text.Lower(t)
)
),
type text
)
in
ResultPower Query solution 7 for Toggle Alphabet Case, proposed by Sue Bayes:
let
Upper = {"A".."Z"}&{"a".."z"}&{" "},
Lower = {"a".."z"}&{"A".."Z"}&{" "},
ListReplace = List.Zip({Upper, Lower}),
Source = Data,
TextToList = Table.AddColumn(Source, "TextToList", each Text.ToList([String])),
Combine_Replace = Table.SelectColumns(Table.AddColumn(TextToList, "Answer", each Text.Combine(List.ReplaceMatchingItems([TextToList], ListReplace), "")), {"String", "Answer"}),
Type = Table.TransformColumnTypes(Combine_Replace,{{"String", type text}, {"Answer", type text}})
in
Type
Melissa de Korte I'm rather chuffed with this! 😁
Solving the challenge of Toggle Alphabet Case with Excel
Excel solution 1 for Toggle Alphabet Case, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
C,
MID(
x,
ROW(
1:99
),
1
),
CONCAT(
IF(
CODE(
C&" "
)>90,
UPPER(
C
),
LOWER(
C
)
)
)
)
)
)Excel solution 2 for Toggle Alphabet Case, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
C,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IF(
ABS(
CODE(
C
)-77.5
)<13,
LOWER(
C
),
UPPER(
C
)
)
)
)
)
)Excel solution 3 for Toggle Alphabet Case, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
L,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IF(
EXACT(
L,
LOWER(
L
)
),
UPPER(
L
),
LOWER(
L
)
)
)
)
)
)Excel solution 4 for Toggle Alphabet Case, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
e,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
IF(
CODE(
e
)<97,
LOWER(
e
),
UPPER(
e
)
)
)
)
)
)
With REDUCE:
=MAP(
A2:A10,
LAMBDA(
x,
REDUCE(
"",
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
LAMBDA(
i,
c,
i&IF(
CODE(
c
)<97,
LOWER(
c
),
UPPER(
c
)
)
)
)
)
)Excel solution 5 for Toggle Alphabet Case, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
A,
LET(
e,
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
),
CONCAT(
IF(
CODE(
e
)<91,
LOWER(
e
),
PROPER(
e
)
)
)
)
)
)Excel solution 6 for Toggle Alphabet Case, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
A2:A10,
"([A-Z])|([a-z])",
"L$1U$2"
)Excel solution 7 for Toggle Alphabet Case, proposed by Julian Poeltl:
=MAP(A2:A10,
LAMBDA(T,
LET(SP,
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
),
C,
CODE(
SP
),
TRIM(CONCAT(CHAR(IFS((C>64)*(C<91),
C+32,
(C>96)*(C<123),
C-32,
1,
C)))))))Excel solution 8 for Toggle Alphabet Case, proposed by Aditya Kumar Darak 🇮🇳:
= MAP(
TRIM(
A2:A10
),
LAMBDA(
a,
LET(
_splt,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
IF(
EXACT(
_splt,
LOWER(
_splt
)
),
UPPER(
_splt
),
LOWER(
_splt
)
)
)
)
)
)Excel solution 9 for Toggle Alphabet Case, proposed by Timothée BLIOT:
=CONCAT(
LET(
Word,
A2,
Characters,
MID(
Word,
SEQUENCE(
LEN(
Word
)
),
1
),
UpperLower,
IF(
EXACT(
UPPER(
Characters
),
Characters
),
LOWER(
Characters
),
UPPER(
Characters
)
),
UpperLower
)
)Excel solution 10 for Toggle Alphabet Case, proposed by Jardiel Euflázio:
=BYROW(
A2:A10,
LAMBDA(
f,
LET(
a,
LEN(
f
),
b,
SEQUENCE(
a
),
c,
MID(
f,
b,
1
),
d,
SEQUENCE(
26,
,
97
),
e,
SEQUENCE(
26,
,
65
),
CONCAT(
IFERROR(
CHAR(
XLOOKUP(
CODE(
c
),
VSTACK(
d,
e
),
VSTACK(
e,
d
)
)
),
c
)
)
)
)
)Excel solution 11 for Toggle Alphabet Case, proposed by Cary Ballard, DML:
=MAP(A2:A10,
LAMBDA(a,
LET(b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(IF((CODE(
b
) < 97),
LOWER(
b
),
UPPER(
b
))))))Excel solution 12 for Toggle Alphabet Case, proposed by RIJESH T.:
=MAP(
A2:A10,
LAMBDA(
s,
LET(
a,
MID(
s,
SEQUENCE(
LEN(
s
)
),
1
),
CONCAT(
IF(
EXACT(
a,
LOWER(
a
)
),
UPPER(
a
),
LOWER(
a
)
)
)
)
)
)Excel solution 13 for Toggle Alphabet Case, proposed by Sarun Chimamphant:
=LET(
a,
A2:A10,
BYROW(
a,
LAMBDA(
b,
LET(
c,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
CONCAT(
BYROW(
c,
LAMBDA(
x,
CHAR(
CODE(
x
)+LOOKUP(
CODE(
x
),
{0,
65,
97,
123},
{0,
32,
-32,
0}
)
)
)
)
)
)
)
)
)Excel solution 14 for Toggle Alphabet Case, proposed by Fábio Gatti:
=LAMBDA(
Word,
LET(
vLen,
LEN(
Word
),
vSeq,
SEQUENCE(
vLen
),
vSplit,
MID(
Word,
vSeq,
1
),
vSameUpper,
EXACT(
vSplit,
UPPER(
vSplit
)
),
vTreat,
IF(
vSam&eUpper,
LOWER(
vSplit
),
UPPER(
vSplit
)
),
vCombine,
CONCAT(
vTreat
),
vCombine
)
)(A1)Excel solution 15 for Toggle Alphabet Case, proposed by Nazmul Islam Jobair:
=BYROW(
A2:A10,
LAMBDA(
r,
CONCAT(
LET(
_lett,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
IF(
CODE(
_lett
) < 91,
LOWER(
_lett
),
UPPER(
_lett
)
)
)
)
)
)Excel solution 16 for Toggle Alphabet Case, proposed by Stevenson Yu:
=LET(
A,
A1,
B,
SEQUENCE(
26,
,
65
),
C,
SEQUENCE(
26,
,
97
),
D,
CODE(
MID(
A,
SEQUENCE(
LEN(
A
)
),
1
)
),
E,
VLOOKUP(
D,
VSTACK(
HSTACK(
B,
C
),
HSTACK(
C,
B
)
),
2,
0
),
CONCAT(
CHAR(
IFERROR(
E,
D
)
)
)
)