Work out the indexes where capital letters are. Remove the spaces. Make sentences lower case. Now, capitalize at those same indexes where the capital letters were. Ex. He rAn = Capital letters at indexes 1,5 => HerAn =>HeraN
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 594
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reapply Original Capitalization with Power Query
Power Query solution 1 for Reapply Original Capitalization, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
W = [Sentences],
Rp = Text.Lower(Text.Remove(W, " ")),
L1 = Text.ToList(W),
L2 = Text.ToList(Rp),
Z = List.Zip({L1, L2}),
T = List.Transform(
Z,
(f) => if Text.Trim(f{0}) = Text.Upper(f{0}) then Text.Upper(f{1}) else f{1}
),
R = Text.Combine(T)
][R]
)
in
ReturnPower Query solution 2 for Reapply Original Capitalization, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
k = List.Transform,
s = List.Contains,
a = Text.ToList([Sentences]),
b = List.Zip({a, List.Positions(a)}),
c = List.Select(b, each s({"A" .. "Z"}, _{0})),
d = k(c, each _{1}),
e = k(List.Select(a, each _ <> " "), Text.Lower),
f = k({0 .. List.Count(e) - 1}, each if s(d, _) then Text.Upper(e{_}) else e{_}),
g = Text.Combine(f)
in
g
)[[Answer]]
in
SolPower Query solution 3 for Reapply Original Capitalization, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = List.Zip({Text.ToList([Sentences]), {0 .. Text.Length([Sentences]) - 1}}),
b = List.Transform(a, each if List.Contains({"A" .. "Z"}, _{0}) then _{1} else null),
c = Text.Lower(Text.Remove([Sentences], " ")),
d = List.Select(List.RemoveNulls(b), each _ < Text.Length(c)),
e = List.Accumulate(
{0 .. Text.Length(c) - 1},
"",
(x, y) => x & (if List.Contains(d, y) then Text.Upper(Text.At(c, y)) else Text.At(c, y))
)
][e]
),
Result = Table.AddColumn(AddCol, "Check", each [Answer Expected] = [My Answer])
in
ResultPower Query solution 4 for Reapply Original Capitalization, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = (x) =>
List.TransformMany(
x,
each {Text.PositionOfAny(_, {"A" .. "Z"}, 2)},
(x, y) =>
Text.Combine(
List.Transform(
let
a = Text.ToList(Text.Lower(Text.Remove(x, " ")))
in
List.Zip({a, {0 .. List.Count(a) - 1}}),
each if List.Contains(y, _{1}) then Text.Upper(_{0}) else _{0}
)
)
){0},
C = Table.FromColumns({Table.ToList(A, B)}, {"Answer"})
in
CPower Query solution 5 for Reapply Original Capitalization, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Answer Expected",
each [
A = _[Sentences],
B = Text.ToList(A),
C = List.PositionOfAny(B, {"A" .. "Z"}, Occurrence.All),
D = Text.Remove(A, " "),
E = Text.Lower(D),
F = Text.ToList(E),
G = List.Transform(
{0 .. List.Count(F) - 1},
each if List.Contains(C, _) then Text.Upper(F{_}) else F{_}
),
H = Text.Combine(G)
][H]
)
in
ResultPower Query solution 6 for Reapply Original Capitalization, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Sentences", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each [
positions = Text.PositionOfAny([Sentences], {"A" .. "Z"}, Occurrence.All),
lower = Text.Remove(Text.Lower([Sentences]), {" "}),
upper = List.Accumulate(
positions,
lower,
(txt, pos) => Text.ReplaceRange(txt, pos, 1, Text.Upper(Text.Middle(txt, pos, 1)))
)
][upper],
type text
)
in
#"Added Custom"Solving the challenge of Reapply Original Capitalization with Excel
Excel solution 1 for Reapply Original Capitalization, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(s,
SEQUENCE(
LEN(
a
)
),
m,
MID(
a,
s,
1
),
n,
MID(
SUBSTITUTE(
a,
" ",
),
s,
1
),
CONCAT(IF((CODE(
m
)<91)*(m>" "),
UPPER(
n
),
LOWER(
n
))))))Excel solution 2 for Reapply Original Capitalization, proposed by Rick Rothstein:
=MAP(A2:A10,
LAMBDA(c,
LET(l,
LOWER(
SUBSTITUTE(
c,
" ",
)
),
REDUCE(l,
SEQUENCE(
LEN(
l
)
),
LAMBDA(a,
x,
LET(m,
MID(
c,
x,
1
),
d,
MID(
a,
x,
1
),
IF((m=" ")+(CODE(
m
)>91),
REPLACE(
a,
x,
1,
d
),
REPLACE(
a,
x,
1,
UPPER(
d
)
))))))))Excel solution 3 for Reapply Original Capitalization, proposed by John V.:
=MAP(A2:A10,
LAMBDA(x,
LET(s,
SEQUENCE(
LEN(
x
)
),
i,
MID(
x,
s,
1
),
c,
i>" ",
f,
FILTER(
i,
c
),
CONCAT(IFNA(IF(c*(CODE(
i
)<91),
UPPER(
f
),
LOWER(
f
)),
"")))))Excel solution 4 for Reapply Original Capitalization, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(x,LET(a,SEQUENCE(LEN(x)),b,MID(x,a,1),c,TOCOL(a/(EXACT(b,UPPER(b))*(b<>" ")),3),d,FILTER(LOWER(b),b<>" "),CONCAT(IFERROR(IF(XMATCH(TAKE(a,ROWS(d)),c),UPPER(d)),d)))))Excel solution 5 for Reapply Original Capitalization, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(B,LET(S,LAMBDA(A,MID(A,SEQUENCE(LEN(A)),1)),A,S(B),W,SUBSTITUTE(B," ",""),CONCAT(IF(DROP((EXACT(UPPER(A),A))*(A<>" "),-(LEN(B)-LEN(W))),UPPER(S(W)),LOWER(S(W)))))))Excel solution 6 for Reapply Original Capitalization, proposed by Aditya Kumar Darak 🇮🇳:
=MAP(
A2:A10,
LAMBDA(a,
LET(
rplc,
SUBSTITUTE(
a,
" ",
""
),
lwr,
LOWER(
rplc
),
len,
LEN(
lwr
),
seq,
SEQUENCE(
len
),
splt1,
MID(
a,
seq,
1
),
splt2,
MID(
lwr,
seq,
1
),
chng,
IF(
EXACT(
splt1,
UPPER(
splt1
)
) * (splt1 <> " "),
UPPER(
splt2
),
splt2
),
rslt,
CONCAT(
chng
),
rslt
)
)
)Excel solution 7 for Reapply Original Capitalization, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(F,LAMBDA(n,MID(n,SEQUENCE(LEN(n)),1)),A,F(z),B,F(SUBSTITUTE(z," ","")),CONCAT(IF(TAKE(MAP(A,LAMBDA(x, IF(x<>" ",EXACT(x,UPPER(x))))),ROWS(B)),UPPER(B),LOWER(B))))))Excel solution 8 for Reapply Original Capitalization, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
w,
LOWER(
SUBSTITUTE(
a,
" ",
)
),
IFERROR(
REDUCE(
w,
TOCOL(
IFS(
ABS(
CODE(
MID(
a,
s,
1
)
)-61
)<29,
s
),
2
),
LAMBDA(
i,
x,
REPLACE(
i,
x,
1,
UPPER(
MID(
w,
x,
1
)
)
)
)
),
w
)
)
)
)Excel solution 9 for Reapply Original Capitalization, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A10,LAMBDA(a,LET(E,REGEXEXTRACT,b,REGEXTEST(E(a,".",1),"[A-Z]"),c,E(LOWER(SUBSTITUTE(a," ","")),".",1),CONCAT(IFNA(IF(b,UPPER(c),c),"")))))Excel solution 10 for Reapply Original Capitalization, proposed by Md. Zohurul Islam:
=MAP(
A2:A10,
LAMBDA(
p,
LET(
q,
SUBSTITUTE(
p,
" ",
""
),
a,
SEQUENCE(
LEN(
p
)
),
b,
MID(
p,
a,
1
),
d,
MID(
q,
a,
1
),
upr,
UPPER(
d
),
lwr,
LOWER(
d
),
cde,
CODE(
b
),
g,
MAP(
cde,
upr,
lwr,
LAMBDA(
x,
y,
z,
IF(
AND(
x<91,
x<>32
),
y,
z
)
)
),
j,
CONCAT(
g
),
j
)
)
)Excel solution 11 for Reapply Original Capitalization, proposed by Jaroslaw Kujawa:
=DROP(REDUCE("";
A2:A10;
LAMBDA(a;
x;
VSTACK(a;
REDUCE("";
x;
LAMBDA(a;
x;
LET(seq;
SEQUENCE(
LEN(
x
)
);
y;
MID(
x;
seq;
1
);
idx;
IF((CODE(
y
)>64)*(CODE(
y
)<91);
a&seq;
a);
maly;
LOWER(
SUBSTITUTE(
x;
" ";
)
);
CONCAT(
IFNA(
IF(
idx="";
MID(
maly;
SEQUENCE(
LEN(
maly
)
);
1
);
UPPER(
MID(
maly;
SEQUENCE(
LEN(
maly
)
);
1
)
)
);
""
)
)))))));
1)Excel solution 12 for Reapply Original Capitalization, proposed by Jaroslaw Kujawa:
=BYROW(A2:A10;LAMBDA(x;LET(seq;SEQUENCE(LEN(x));y;MID(x;seq;1);idx;IF((CODE(y)>64)*(CODE(y)<91);seq;);kr;LOWER(SUBSTITUTE(x;" ";));maly;MID(kr;SEQUENCE(LEN(kr));1);CONCAT(IFNA(IF(SEQUENCE(LEN(kr))=idx;UPPER(maly);maly);"")))))Excel solution 13 for Reapply Original Capitalization, proposed by Ankur Sharma:
=MAP(A2:A10, LAMBDA(z,
LET(s, SEQUENCE, L, LEN,
a, MID(z, s(L(z)), 1),
b, s(L(z)),
c, (a <> " ") * EXACT(a, UPPER(a)) * b,
d, LOWER(SUBSTITUTE(z, " ", "")),
CONCAT(MAP(MID(d, s(L(d)), 1), s(L(d)), LAMBDA(y,z, IF(FILTER(c, c = z, 0), UPPER(y), y)))))))Excel solution 14 for Reapply Original Capitalization, proposed by JvdV -:
=MAP(A2:A10,LAMBDA(s,LET(f,REGEXEXTRACT,x,f(LOWER(s),"S",1),CONCAT(IFNA(IF(REGEXTEST(f(s,".",1),"[A-Z]"),UPPER(x),x),"")))))Excel solution 15 for Reapply Original Capitalization, proposed by Imam Hambali:
=MAP(A2:A10,
LAMBDA(s,
LET(
seq,
SEQUENCE(
,
LEN(
s
)
),
sm,
MID(
s,
seq,
1
),
c,
CODE(
sm
),
ch,
IF((c>65)*(c<=90),
seq,
0),
fs,
FILTER(
LOWER(
sm
),
c<>32
),
f,
IF(
NOT(
ISNA(
XMATCH(
seq,
FILTER(
ch,
ch>0
)
)
)
),
UPPER(
fs
),
LOWER(
fs
)
),
CONCAT(
FILTER(
f,
NOT(
ISNA(
f
)
)
)
)
))
)Excel solution 16 for Reapply Original Capitalization, proposed by Edwin Tisnado:
=MAP(
A2:A10,
LAMBDA(
u,
LET(
s,
SEQUENCE(
30
),
a,
MID(
u,
s,
& 1
),
TEXTJOIN(
,
,
MAP(
IF(
a=" ",
,
CODE(
a
)=CODE(
UPPER(
a
)
)
),
MID(
LOWER(
SUBSTITUTE(
u,
" ",
)
),
s,
1
),
LAMBDA(
x,
y,
IFERROR(
IF(
x,
UPPER(
y
),
y
),
""
)
)
)
)
)
)
)Excel solution 17 for Reapply Original Capitalization, proposed by Md Ismail Hosen:
=LAMBDA(sentences,
LET(TOCHARS,
LAMBDA(
InputText,
IF(
InputText="",
"",
MID(
InputText,
SEQUENCE(
LEN(
InputText
)
),
1
)
)
),
CustomCapitalizer,
LAMBDA(Sentence,
LET(\LambdaName,
"CustomCapitalizer",
_Chars,
TOCHARS(
Sentence
),
_WithoutSpace,
LOWER(
SUBSTITUTE(
Sentence,
" ",
""
)
),
_CapsIndexes,
EXACT(
UPPER(
_Chars
),
_Chars
)*(_Chars<>" "),
_NewChars,
TOCHARS(
_WithoutSpace
),
_Result,
CONCAT(
IF(
TAKE(
_CapsIndexes,
ROWS(
_NewChars
)
)=1,
UPPER(
_NewChars
),
_NewChars
)
),
_Result)),
MAP(
sentences,
CustomCapitalizer
)))(A2:A10)Excel solution 18 for Reapply Original Capitalization, proposed by Gabriel Pugliese:
=MAP(A2:A10,LAMBDA(z,
LET(Ext,LAMBDA(x,MID(x,SEQUENCE(LEN(x)),1)),
i,(CODE(Ext(z))>32)*(CODE(Ext(z))<91),
q,LOWER(CONCAT(TEXTSPLIT(z," "))),
i,DROP(i,-(ROWS(i)-ROWS(Ext(q)))),
CONCAT(IF(i,UPPER(Ext(q)),Ext(q))))))Excel solution 19 for Reapply Original Capitalization, proposed by Ben Gutscher:
=MAP(A2:A10,
LAMBDA(R,
CONCAT(IFERROR(LET(L,
MID(
R,
SEQUENCE(
LEN(
R
)
),
1
),
C,
(CODE(
L
)<=90)*(L<>" "),
NS,
LOWER(
SUBSTITUTE(
R,
" ",
""
)
),
CHAR(
CODE(
MID(
NS,
SEQUENCE(
LEN(
NS
)
),
1
)
)-32*C
)),
""))))Solving the challenge of Reapply Original Capitalization with Python
Python solution 1 for Reapply Original Capitalization, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
path = "594 Capitalize at Same Indexes.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
def capitalize_at_indexes(sentence):
pos = [m.start() for m in re.finditer(r'[A-Z]', sentence)]
answer = list(re.sub(r'[^a-zA-Z]', '', sentence).lower())
answer = [char.upper() if i in pos else char for i, char in enumerate(answer)]
return ''.join(answer)
input['answer'] = input['Sentences'].apply(capitalize_at_indexes)
print(input['answer'].equals(test['Answer Expected'])) # True
Solving the challenge of Reapply Original Capitalization with Python in Excel
Python in Excel solution 1 for Reapply Original Capitalization, proposed by Anshu Bantra:
def change_case(string: str) -> str:
caps_ = [idx for idx, val in enumerate(string) if val.isupper()]
return ''.join([ val.upper() if idx in caps_ else val
for idx, val in enumerate(string.lower().replace(' ', ''))]
)
df = xl("A1:A10", headers=True)
df['Answer'] = df['Sentences'].apply(change_case)
df
&&