This problem is an extension of first challenge. This is a case sensitive problem. A/a, B/b, C/c…..Y/y, Z/z will be replaced with Z/z, Y/y, X/x….B/b, A/a. 0..9 will be replaced by 9..0. Any character other than English letters and numbers will not be replaced.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 247
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Case-Sensitive Reverse Cipher with Power Query
Power Query solution 1 for Case-Sensitive Reverse Cipher, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
Text.ToList([Text]),
each try
Text.From(9 - Number.From(_)) ?? " "
otherwise
if _ < "A" then
_
else
List.Reverse({"A" .. "Z", "a" .. "z"}){List.PositionOf({"a" .. "z", "A" .. "Z"}, _)}
)
)
)
in
AnsPower Query solution 2 for Case-Sensitive Reverse Cipher, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
l = {{"0" .. "9"}, {"A" .. "Z"}, {"a" .. "z"}},
r = List.Transform(l, List.Reverse)
in
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Text]),
List.Zip({List.Combine(l), List.Combine(r)})
)
)
)
in
SPower Query solution 3 for Case-Sensitive Reverse Cipher, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.TransformColumns(
Source,
{
"Text",
each
let
a = List.Zip(
{
{"A" .. "Z"} & {"a" .. "z"} & {"0" .. "9"},
List.Reverse({"A" .. "Z"}) & List.Reverse({"a" .. "z"}) & List.Reverse({"0" .. "9"})
}
),
b = Text.ToList(_)
in
Text.Combine(List.ReplaceMatchingItems(b, a))
}
)
in
SolPower Query solution 4 for Case-Sensitive Reverse Cipher, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
sub = List.Zip(
{{"0" .. "9", "a" .. "z", "A" .. "Z"}, List.Reverse({"A" .. "Z", "a" .. "z", "0" .. "9"})}
),
res = Table.TransformColumns(
Fonte,
{{"Text", each Text.Combine(List.ReplaceMatchingItems(Text.ToList(_), sub))}}
)
in
resSolving the challenge of Case-Sensitive Reverse Cipher with Excel
Excel solution 1 for Case-Sensitive Reverse Cipher, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(IFERROR(9-m,
IF(m<"a",
m,
CHAR(100-DECIMAL(
m,
36
)+(CODE(
m
)>91)*32)))))))Excel solution 2 for Case-Sensitive Reverse Cipher, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
z,
REDUCE(
"",
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
LAMBDA(
a,
x,
LET(
l,
CONCAT(
CHAR(
SEQUENCE(
26,
,
65
)
)
),
m,
MID(
l,
27-SEARCH(
x,
l
),
1
),
a&IF(
ISNUMBER(
0+x
),
9-x,
IF(
ISNUMBER(
FIND(
UPPER(
x
),
l
)
),
IF(
CODE(
x
)>90,
LOWER(
m
),
m
),
x
)
)
)
)
)
)
)Excel solution 3 for Case-Sensitive Reverse Cipher, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
t,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
r,
ROW(
1:36
),
c,
BASE(
r-1,
36
),
b,
IFNA(
XLOOKUP(
t,
c,
SORTBY(
c,
MOD(
10-r,
36
)
)
),
t
),
CONCAT(
IF(
CODE(
t
)>90,
LOWER(
b
),
b
)
)
)
)
)Excel solution 4 for Case-Sensitive Reverse Cipher, proposed by محمد حلمي:
=MAP(A2:A10,
LAMBDA(a,
LET(i,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
r,
(i>47)*(i<58),
CONCAT(CHAR(IF((i>64)*(i<91)+(i>96)*(i<123)+r,
r*-18+91-MOD(
i,
32
)+(i>90)*32,
i))))))Excel solution 5 for Case-Sensitive Reverse Cipher, proposed by محمد حلمي:
=MAP(A2:A10,
LAMBDA(a,
LET(i,
CODE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
CONCAT(CHAR(IFS((i>64)*(i<91),
91-MOD(
i+26,
90
),
(i>96)*(i<123),
123-MOD(
i+26,
122
),
(i>47)*(i<58),
58-MOD(
i+10,
57
),
1,
i))))))Excel solution 6 for Case-Sensitive Reverse Cipher, proposed by Kris Jaganah:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
SEQUENCE(
10,
,
0
)
),
b,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
c,
IFNA(
XLOOKUP(
b,
TEXT(
a,
"0"
),
SORT(
a,
,
-1
)
),
b
),
CONCAT(
IF(
EXACT(
b,
LOWER(
b
)
),
LOWER(
c
),
c
)
)
)
)
)Excel solution 7 for Case-Sensitive Reverse Cipher, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
T,
LET(
SP,
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
),
M,
MAP(
SP,
LAMBDA(
A,
XLOOKUP(
A,
CHAR(
VSTACK(
SEQUENCE(
26,
,
65
),
SEQUENCE(
10,
,
48
)
)
),
CHAR(
VSTACK(
SEQUENCE(
26,
,
90,
-1
),
SEQUENCE(
10,
,
57,
-1
)
)
),
A
)
)
),
CONCAT(
IF(
EXACT(
SP,
UPPER(
SP
)
),
M,
LOWER(
M
)
)
)
)
)
)Excel solution 8 for Case-Sensitive Reverse Cipher, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(A,MID(z,SEQUENCE(LEN(z)),1),D,VSTACK(SEQUENCE(10,,0),CHAR(SEQUENCE(26,,65)),CHAR(SEQUENCE(26,,97))),E,VSTACK(SEQUENCE(10,,9,-1),CHAR(SEQUENCE(26,,90,-1)),CHAR(SEQUENCE(26,,122,-1))),CONCAT(MAP(A,LAMBDA(x,XLOOKUP(TRUE,EXACT(D,x),E,x)))))))Excel solution 9 for Case-Sensitive Reverse Cipher, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
TEXT(
SEQUENCE(
10,
,
0
),
"@"
)
),
b,
VSTACK(
CHAR(
SEQUENCE(
26,
,
90,
-1
)
),
SEQUENCE(
10,
,
9,
-1
)
),
c,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
d,
EXACT(
c,
UPPER(
c
)
),
e,
IFERROR(
XLOOKUP(
c,
a,
b
),
c
),
CONCAT(
IF(
d,
e,
LOWER(
e
)
)
)
)
)
)Excel solution 10 for Case-Sensitive Reverse Cipher, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
LAMBDA(a,
LET(_s,
SEQUENCE(
36,
,
65
),
_d,
TEXTSPLIT(UPPER(
a
),
,
CHAR(_s-43*(_s>90)),
1),
TEXTJOIN(_d,
,
MAP(TEXTSPLIT(
a,
,
_d
),
LAMBDA(b,
LET(_c,
CODE(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
)
),
IFERROR(CONCAT(CHAR(105+50*(_c>57)+64*(_c>96)-_c)),
" "))))))))Excel solution 11 for Case-Sensitive Reverse Cipher, proposed by Sunny Baggu:
=LET(
_LHS,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
,
97
)
),
VALUETOTEXT(
SEQUENCE(
10,
,
0
)
)
),
_RHS,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65 + 25,
-1
)
),
CHAR(
SEQUENCE(
26,
,
97 + 25,
-1
)
),
VALUETOTEXT(
SEQUENCE(
10,
,
9,
-1
)
)
),
MAP(
A2:A10,
LAMBDA(
t,
CONCAT(
MAP(
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
LAMBDA(
a,
FILTER(
_RHS,
EXACT(
a,
_LHS
),
a
)
)
)
)
)
)
)Excel solution 12 for Case-Sensitive Reverse Cipher, proposed by Asheesh Pahwa:
=LET(a,E2:E9&,
alphC,SEQUENCE(26,,65), revalphC,SEQUENCE(26,,90,-1),
alphS,SEQUENCE(26,,97),
revaphs,SEQUENCE(26,,122,-1),
num,SEQUENCE(10,,0),
revnum,SEQUENCE(10,,9,-1), stacks,HSTACK(VSTACK(alphC,alphs,num), VSTACK(revalphC,revaphS,revnum)),b,TEXTJOIN("|",,a), c,TEXTSPLIT(b,"","",""),
d,MAP(c,LAMBDA(x,IFERROR(CONCAT(IFERROR(CHAR(XLOOKUP(CODE(MID(x,SEQUENCE(LEN(x)),1)), TAKE(stacks,,1),TAKE(stacks,,-1))),CHAR(CODE(MID(x,SEQUENCE(LEN(x)),1))))),""))),e,MAP(d,LAMBDA(x,IFERROR(CONCAT(IFERROR(XLOOKUP(--MID(X,SEQUENCE(LEN(x)),1),TAKE(stacks,,1),TAKE(stacks,,-1)),MID(X,SEQUENCE(LEN(x)),1))),x))),f,BYROW(e,LAMBDA(x,TEXTJOIN("",TRUE,x))),f)Excel solution 13 for Case-Sensitive Reverse Cipher, proposed by Charles Roldan:
=MAP(A2:A10, LAMBDA(x, LET(y, {1,48,58,65,91,97,123,256}, n, CODE(MID(x, SEQUENCE(LEN(x)), 1)), k, MATCH(n, y), CONCAT(CHAR(IF(MOD(k, 2), n, INDEX(y, k) + INDEX(y, k + 1) - 1 - n))))))Excel solution 14 for Case-Sensitive Reverse Cipher, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=MAP(
A2:A10;
LAMBDA(
q;
TEXTJOIN(
;
;
LET(
a;
CHAR(
ROW(
A97:A122
)
);
b;
CHAR(
ROW(
A65:A90
)
);
c;
CHAR(
ROW(
A48:A57
)
);
IFERROR(
MAP(
MID(
q;
SEQUENCE(
LEN(
q
)
);
1
);
LAMBDA(
p;
FILTER(
VSTACK(
MID(
TEXTJOIN(
;
;
a
);
LEN(
TEXTJOIN(
;
;
a
)
)-SEQUENCE(
LEN(
TEXTJOIN(
;
;
a
)
)
)+1;
1
);
MID(
TEXTJOIN(
;
;
b
);
LEN(
TEXTJOIN(
;
;
b
)
)-SEQUENCE(
LEN(
TEXTJOIN(
;
;
b
)
)
)+1;
1
);
MID(
TEXTJOIN(
;
;
c
);
LEN(
TEXTJOIN(
;
;
c
)
)-SEQUENCE(
LEN(
TEXTJOIN(
;
;
c
)
)
)+1;
1
)
);
ISNUMBER(
FIND(
p;
VSTACK(
a;
b;
c
);
1
)
)
)
)
);
MID(
q;
SEQUENCE(
LEN(
q
)
);
1
)
)
)
)
)
)Excel solution 15 for Case-Sensitive Reverse Cipher, proposed by Julien Lacaze:
=LET(data,A2:A10,
revCol,LAMBDA(column,
INDEX(column,SEQUENCE(ROWS(column),,ROWS(column),-1))),
alphaCap,CHAR(SEQUENCE(26,,CODE("A"))),
alphaLow,CHAR(SEQUENCE(26,,CODE("a"))),
numSeq,TEXT(SEQUENCE(10,,0),"0"),
base,VSTACK(alphaCap,alphaLow,numSeq),
output,VSTACK(revCol(alphaCap),revCol(alphaLow),revCol(numSeq)),
MAP(data,LAMBDA(a,LET(
s,MID(a,SEQUENCE(LEN(a)),1),
CONCAT(IFERROR(XLOOKUP(CODE(s),CODE(base),output,,0),s))))))Excel solution 16 for Case-Sensitive Reverse Cipher, proposed by Giorgi Goderdzishvili:
=
MAP(A2:A10,LAMBDA(x,
LET(
txt,x,
cr,MID(txt,SEQUENCE(,LEN(txt)),1),
fn,IFERROR(--cr,cr),
upr,UPPER(cr),
isTx,IF( (CODE(upr)>64)*(CODE(upr)<91),1,0),
isnm, ISNUMBER(fn),
isUp,IFERROR( (CODE(cr)>64)*(CODE(cr)<91),0),
rev,
IF(isnm,9-fn,
IF(isTx,CHAR(91-CODE(UPPER(fn))+64),fn)),
CONCAT(IF(isUp,UPPER(rev),LOWER(rev))))))Excel solution 17 for Case-Sensitive Reverse Cipher, proposed by Daniel Garzia:
=MAP(A2:A10,
LAMBDA(x,
LET(c,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
t,
64+32*(c>96),
l,
ROW(
1:26
),
CONCAT(
CHAR(
IFNA(
IFS(
c<58,
47+XMATCH(
c,
SEQUENCE(
10,
,
57,
-1
)
),
c>64,
t+XLOOKUP(
MOD(
c,
t
),
l,
SORT(
l,
,
-1
)
)
),
c
)
)
))))Excel solution 18 for Case-Sensitive Reverse Cipher, proposed by Quadri Olayinka Atharu:
=MAP(A2:A10,
LAMBDA(t,
LET(m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
l,
CHAR(
SEQUENCE(
26,
,
90,
-1
)
),
a,
IFNA(
CHAR(
XMATCH(
m,
l
)+64
),
m
),
b,
IFERROR(9-(--m),
a),
CONCAT(IF((CODE(
m
)>64)*CODE(
m
)<97,
b,
LOWER(
b
))))))Excel solution 19 for Case-Sensitive Reverse Cipher, proposed by Quadri Olayinka Atharu:
=MAP(A2:A10,
LAMBDA(t,
LET(m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
l,
CHAR(
SEQUENCE(
26,
,
65
)
),
a,
IFNA(
INDEX(
l,
27-XMATCH(
m,
l
)
),
m
),
b,
9-(--m),
c,
IF(
ISERR(
b
),
a,
b
),
CONCAT(IF((CODE(
m
)>64)*CODE(
m
)<97,
c,
LOWER(
c
))))))Excel solution 20 for Case-Sensitive Reverse Cipher, proposed by Quadri Olayinka Atharu:
=MAP(A2:A10,
LAMBDA(t,
LET(m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
l,
CHAR(
SEQUENCE(
26,
,
90,
-1
)
),
a,
IFNA(
CHAR(
XMATCH(
m,
l
)+64
),
m
),
b,
IFERROR(9-(--m),
a),
CONCAT(
IF(
EXACT(
UPPER(
m
),
m
),
b,
LOWER(
b
)
)
))))Excel solution 21 for Case-Sensitive Reverse Cipher, proposed by Henriette Hamer:
=LET(_lowA;CODE("a");_lowZ;CODE("z");_highA;CODE("A");_highZ;CODE("Z");_number0;CODE(0);_number9;CODE(9);MAP(A2:A10;LAMBDA(_b;CONCAT(MAP(CODE(MID(_b;SEQUENCE(;LEN(_b));1));LAMBDA(_a;LET(_text;_a;LET(_lower;AND(_text<=_lowZ;_text>=_lowA);_upper;AND(_text<=_highZ;_text>=_highA);_number;AND(_text<=_number9;_text>=_number0);IF(_lower;CHAR(_lowZ-_text+_lowA);IF(_upper;CHAR(_highZ-_text+_highA);IF(_number;CHAR(_number9-_text+_number0);CHAR(_text))))))))))))Excel solution 22 for Case-Sensitive Reverse Cipher, proposed by Harry Seiders:
=LET(
TO,
VSTACK(
CHAR(
SEQUENCE(
26,
,
90,
-1
)
),
CHAR(
SEQUENCE(
26,
,
122,
-1
)
),
SEQUENCE(
10,
,
9,
-1
)
),
FROM,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
,
97
)
),
SEQUENCE(
10,
,
0
)
),
MAP(
A2:A10,
LAMBDA(
X,
LET(
Z,
MID(
X,
SEQUENCE(
LEN(
X
)
),
1
),
CONCAT(
MAP(
Z,
LAMBDA(
Y,
XLOOKUP(
TRUE,
EXACT(
Y,
FROM
),
TO,
Y
)
)
)
)
)
)
)
)Solving the challenge of Case-Sensitive Reverse Cipher with Excel VBA
Excel VBA solution 1 for Case-Sensitive Reverse Cipher, proposed by Nicolas Micot:
Function f_AtbashCipher(ByVal texte As String) As String
Dim resultat As String, lettre As String
Dim codeMin As Integer, codeMax As Integer
For i = 1 To Len(texte)
lettre = Mid(texte, i, 1)
Select Case lettre
Case "A" To "Z"
codeMin = Asc("A")
codeMax = Asc("Z")
GoSub applyCipher
Case "a" To "z"
codeMin = Asc("a")
codeMax = Asc("z")
GoSub applyCipher
Case "0" To "9"
codeMin = Asc("0")
codeMax = Asc("9")
GoSub applyCipher
Case Else
resultat = resultat & lettre
End Select
Next i
f_AtbashCipher = resultat
Exit Function
applyCipher:
resultat = resultat & Chr(codeMax - Asc(lettre) + codeMin)
Return
End Function
&&