In the ID column, Replace all instances of “X” if it appears consecutively more than once with just an “X”.
📌 Challenge Details and Links
Challenge Number: 185
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Replace Consecutive X! with Power Query
Power Query solution 1 for Replace Consecutive X!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
_ = Table.TransformColumns(
Source,
{
"ID",
each List.Accumulate(
{0 .. Text.Length(_) - 1},
"",
(b, n) =>
b & {Text.At(_, n), ""}{Byte.From(Text.Upper(Text.End(b, 1) & Text.At(_, n)) = "XX")}
)
}
)
in
_Power Query solution 2 for Replace Consecutive X!, proposed by Luan Rodrigues:
let
Fonte = Table.TransformColumns(
Data,
{
"ID",
each
let
a = Table.FromColumns(
{List.Transform(Text.ToList(_), (x) => if x = "x" then "X" else x)},
{"ID"}
),
b = Text.Combine(Table.Group(a, "ID", {"tab", each _}, 0)[ID], "")
in
b
}
)
in
FontePower Query solution 3 for Replace Consecutive X!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a =
hashtag
#table({"N","O"},{{"X","XX"},{"X","xx"},{"X","xX"},{"X","Xx"}}),
b = List.Accumulate(List.Positions(a[N]),S,(s,c)=>Table.ReplaceValue(s,a[O]{c},a[N]{c},Replacer.ReplaceText,{"ID"})),
Sol = List.Accumulate(List.Positions(a[N]),b,(s,c)=>Table.ReplaceValue(s,a[O]{c},a[N]{c},Replacer.ReplaceText,{"ID"}))
in
SolPower Query solution 4 for Replace Consecutive X!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Sol = Table.TransformColumns(Source,{{"ID", each
let
a = _,
b = Text.ToList(a),
c = Table.FromColumns({b}),
d = Table.Group(c, "Column1", {"A", each _},0,
Comparer.OrdinalIgnoreCase),
e = Text.Combine(d[Column1])
in e}})
in
SolPower Query solution 5 for Replace Consecutive X!, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcwxDoAgDAXQu3TGAAUFLmDCwOL0E8LgAXT2+CbFoa5veL0TW+8sO45kCG0BPNMwnYLyCqAFiEflOU1b9ZFR4IU3zc91n6JJK3bMIv/0w6LwoDFe", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, ID = _t]),
Result = Table.AddColumn(Source, "Exp.Result", each Text.Combine(Table.Group(Table.FromColumns( {Text.ToList([ID])} ),"Column1", {"tmp", (x)=> x },0, Comparer.OrdinalIgnoreCase )[Column1])) [[Date],[Exp.Result]]
in
ResultPower Query solution 6 for Replace Consecutive X!, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
B = Table.TransformColumns(
A,
{
"ID",
each List.Sort(
List.TransformMany(
{1 .. 5},
(z) => {"x", "X", "xX", "Xx"},
(u, v) => Text.Replace(_, Text.Repeat(v, u), "X")
),
{(w) => Text.Length(w)}
){0}
}
)
in
BPower Query solution 7 for Replace Consecutive X!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
Result = Table.TransformColumnTypes(
Table.TransformColumns(
Source,
{
"ID",
each [
a = Text.ToList(_),
b = List.Accumulate(
{1 .. List.Count(a) - 1},
a{0},
(s, c) => if Text.Upper(a{c}) = Text.Upper(a{c - 1}) then s & a{c} else s & ":" & a{c}
),
c = Text.Combine(
List.Transform(
Text.Split(b, ":"),
(t) => if Text.Length(t) > 1 and Text.Contains(Text.Upper(t), "X") then "X" else t
)
)
][c]
}
),
{"Date", type date}
)
in
ResultPower Query solution 8 for Replace Consecutive X!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
ReplaceConsecX = (txt) =>
List.Last(
List.Generate(
() => [r = 0, s = Text.At(txt, 0)],
each [r] < Text.Length(txt),
each [
r = [r] + 1,
c = Text.At(txt, r),
e = Text.Upper(Text.End([s], 1)),
s =
if e = Text.Upper(c) and Text.Upper(c) = "X" then
Text.Start([s], Text.Length([s]) - 1) & "X"
else
[s] & c
]
)
)[s],
Transform = Table.TransformColumns(Source, {"ID", ReplaceConsecX}),
Result = Table.TransformColumnTypes(Transform, {"Date", type date})
in
ResultPower Query solution 9 for Replace Consecutive X!, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
A = Table.AddColumn(
Source,
"TextToList",
each [
a = Text.ToList([ID]),
b = Table.FromList(a, Splitter.SplitByNothing(), {"Text"}),
c = Table.Group(b, {"Text"}, {{"All", each _}}, 0, Comparer.OrdinalIgnoreCase)[Text],
d = Text.Combine(c)
][d]
)
in
Table.RemoveColumns(A, {"ID"})Power Query solution 10 for Replace Consecutive X!, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
Result = Table.TransformColumns(
Source,
{
{"Date", each Date.From(_), type date},
{
"ID",
each List.Accumulate(
Text.ToList(_),
"",
(s, c) =>
s
& (
if Text.EndsWith(s, "x", Comparer.OrdinalIgnoreCase)
and Text.Contains(c, "x", Comparer.OrdinalIgnoreCase)
then
""
else
c
)
)
}
}
)
in
ResultPower Query solution 11 for Replace Consecutive X!, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
Res = Table.TransformColumns(Source, {"ID", Fun}),
Fun = each [
A = Text.ToList(_),
B = Table.Group(
Table.FromList(A, each {_}),
"Column1",
{"tbl", each _},
0,
(x, y) => Number.From(Text.Lower(x) <> Text.Lower(y) or Text.Lower(y) <> "x")
)[Column1],
C = Text.Combine(B)
][C]
in
ResPower Query solution 12 for Replace Consecutive X!, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
Res = Table.TransformColumns(Source, {"ID", Fun}),
Fun = each [
A = Text.ToList(_),
B = List.Accumulate(
A,
"",
(a, v) => if Text.Lower(Text.End(a, 1)) = "x" and Text.Lower(v) = "x" then a else a & v
)
][B]
in
ResPower Query solution 13 for Replace Consecutive X!, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Final = Table.TransformColumns(Source, {"ID", each let
a = Table.FromList(Text.ToList(_), null, {"ID"}),
b = Table.Group(a, "ID", {"All", each Table.RowCount(_)}, 0, Comparer.OrdinalIgnoreCase),
c = Text.Combine(b[ID])
in c})
in
FinalPower Query solution 14 for Replace Consecutive X!, proposed by Vida Vaitkunaite:
letters, their duplicates will be returned, only x duplicates will be removed:
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
Final = Table.TransformColumns(Source, {"ID", each let
a = Table.FromList(Text.ToList(_), null, {"I"}),
b = Table.Group(a, "I", {{"Count", each Table.RowCount(_)}, {"All", each Text.Combine([I], "")}}, 0, Comparer.OrdinalIgnoreCase),
c= Table.AddColumn(b, "ID", each if [Count]>1 and [I] <> "x" and [I] <> "X" then [All] else [I]),
d = Text.Combine(c[ID])
in d})
in
FinalSolving the challenge of Replace Consecutive X! with Excel
Excel solution 1 for Replace Consecutive X!, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
D3:D10,
"x{2,}",
"X",
,
1
)
=MAP(
D3:D10,
LAMBDA(
x,
@SORT(
SUBSTITUTE(
SUBSTITUTE(
x,
"x",
"X"
),
REPT(
"X",
ROW(
2:5
)
),
"X"
)
)
)
)Excel solution 2 for Replace Consecutive X!, proposed by Julian Poeltl:
=MAP(
Data[ID],
LAMBDA(
I,
LET(
R,
SUBSTITUTE(
I,
"x",
"X"
),
L,
LEN(
I
),
REDUCE(
R,
SEQUENCE(
L,
,
L,
-1
),
LAMBDA(
A,
B,
SUBSTITUTE(
A,
REPT(
"X",
B
),
"X"
)
)
)
)
)
)Excel solution 3 for Replace Consecutive X!, proposed by Kris Jaganah:
=REGEXREPLACE(
Data[ID],
"[xX]+",
"X"
)Excel solution 4 for Replace Consecutive X!, proposed by Imam Hambali:
=REDUCE(
SUBSTITUTE(
Data[ID],
"x",
"X"
),
REPT(
"X",
SEQUENCE(
6,
,
2,
0
)
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
"X"
)
)
)Excel solution 5 for Replace Consecutive X!, proposed by Sunny Baggu:
=MAP(
Data[ID], LAMBDA(t, LET(
_m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
), _a,
DROP(
_m,
1
), _b,
DROP(
_m,
-1
), _c,
1 - (_a = _b), LEFT(
t
) & IFERROR(
CONCAT(
FILTER(
_a,
_c
)
),
""
)
)
)
)Excel solution 6 for Replace Consecutive X!, proposed by abdelaziz allam:
=MAP(Data[ID],LAMBDA(a,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a,"x","X"),"XX","X"),"XX","X")))Excel solution 7 for Replace Consecutive X!, proposed by Alejandro Campos:
=REGEXREPLACE(
Data[ID],
"[xX]{2,}",
"X"
)Excel solution 8 for Replace Consecutive X!, proposed by Ankur Sharma:
=MAP(
D3:D10,
LAMBDA(
a, LET(
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
SCAN(
0,
b = "x",
LAMBDA(
d,
e,
SUM(
d,
e
) * e
)
),
TEXTJOIN(
"",
,
IF(
c <= 1,
b,
""
)
)
)
)
)Excel solution 9 for Replace Consecutive X!, proposed by Asheesh Pahwa:
=LET(
s,
SUBSTITUTE(
D3:D10,
"x",
"X"
),
REDUCE(
G2,
s,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
m,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
c,
SEQUENCE(
COUNTA(
m
)
),
CONCAT(
MAP(
c,
LAMBDA(
a,
LET(
I,
INDEX(
m,
a,
),
IFERROR(
IF(
CHOOSEROWS(
m,
a
)=INDEX(
m,
a+1
),
"",
I
),
I
)
)
)
)
)
)
)
)
)
)Excel solution 10 for Replace Consecutive X!, proposed by Bilal Mahmoud kh.:
=MAP(
D3:D10,
LAMBDA(
n,
CONCAT(
REDUCE(
,
MID(
n,
SEQUENCE(
LEN(
n
)
),
1
),
LAMBDA(
x,
y,
IF(
INDEX(
x,
COUNTA(
x
),
1
)=y,
x,
VSTACK(
x,
y
)
)
)
)
)
)
)Excel solution 11 for Replace Consecutive X!, proposed by ferhat CK:
=REGEXREPLACE(Data[ID],"(?i)X{2,}","X")Excel solution 12 for Replace Consecutive X!, proposed by Gabriel Pugliese:
=REGEXREPLACE(
d3:d10,
"[Xx]{2,}",
"X",
0
)Excel solution 13 for Replace Consecutive X!, proposed by Hamidi Hamid:
=LET(
w,
D3:D10,
x,
MID(
w,
SEQUENCE(
,
120
),
1
),
y,
HSTACK(
DROP(
x,
,
1
),
SEQUENCE(
ROWS(
w
)
)
),
s,
BYROW(
HSTACK(
TOCOL(
x
),
TOCOL(
y
)
),
LAMBDA(
a,
ARRAYTOTEXT(
IF(
CONCAT(
a
)="XX",
"X",
a
)
)
)
),
t,
DROP(
REDUCE(
0,
s,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
",",
)
)
)
),
1
),
p,
BYROW(
IFERROR(
WRAPROWS(
IF(
ISERROR(
TAKE(
t,
,
-1
)
),
"",
TAKE(
t,
,
1
)
),
120
),
""
),
CONCAT
),
TOCOL(
IF(
p="",
1/0,
p
),
3
)
)Excel solution 14 for Replace Consecutive X!, proposed by Hamidi Hamid:
=LET(
x,
MID(
D3:D10,
SEQUENCE(
,
20
),
1
),
y,
HSTACK(
DROP(
x,
,
1
),
SEQUENCE(
COUNTA(
D3:D10
)
)
),
HSTACK(
C3:C10,
BYROW(
IF(
y=x,
"",
x
),
CONCAT
)
)
)Excel solution 15 for Replace Consecutive X!, proposed by Hussein SATOUR:
=REGEXREPLACE(
D3:D10,
"X+",
"X",
,
1
)Excel solution 16 for Replace Consecutive X!, proposed by Leonid Koyfman:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((D3:D10),
"x",
"X"),
"X",
"<>"),
"><",
""),
"<>",
"X")Excel solution 17 for Replace Consecutive X!, proposed by Md. Zohurul Islam:
=MAP(
D3:D10,
LAMBDA(
z,
LET(
a,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
b,
VSTACK(
0,
DROP(
a,
1
)=DROP(
a,
-1
)
),
d,
IF(
b,
"",
a
),
e,
IFERROR(
CONCAT(
d
),
z
),
e
)
)
)Excel solution 18 for Replace Consecutive X!, proposed by Meganathan Elumalai:
=LET(
s,
SUBSTITUTE,
HSTACK(
Data[Date],
s(
s(
TRIM(
s(
"|"&s(
Data[ID],
"x",
"X"
)&"|",
"X",
" "
)
),
" ",
"X"
),
"|",
)
)
)Excel solution 19 for Replace Consecutive X!, proposed by Meganathan Elumalai:
=HSTACK(
Data[Date],
REGEXREPLACE(
Data[ID],
"X+",
"X",
,
1
)
)Excel solution 20 for Replace Consecutive X!, proposed by Nicolas Micot:
=LAMBDA(
l_texte;
l_recursion; LET(
_nouveauTexte;
SUBSTITUE(
SUBSTITUE(
SUBSTITUE(
l_texte;
"XX";
"X"
);
"xX";
"X"
);
"Xx";
"X"
); SI(
NBCAR(
_nouveauTexte
) = NBCAR(
l_texte
);
l_texte;
l_recursion(
_nouveauTexte;
l_recursion
)
)
)
)
And then on G3: =f_remplaceXConsécutifs(
D3;
f_remplaceXConsécutifs
)Excel solution 21 for Replace Consecutive X!, proposed by Pieter de B.:
=HSTACK(C3:C10,
REDUCE("",
SEQUENCE(
,
99
),
LAMBDA(x,
y,
LET(z,
MID(
D3:D10,
y,
1
),
IF((z="x")*(RIGHT(
x
)="x"),
x,
x&z)))))Excel solution 22 for Replace Consecutive X!, proposed by Pieter de B.:
=REGEXREPLACE(
C3:D10,
"x+",
"X",
,
1
)Excel solution 23 for Replace Consecutive X!, proposed by Rick Rothstein:
=REDUCE(
SUBSTITUTE(
D3:D10,
"x",
"X"
),
{6,
4,
2,
2},
LAMBDA(
a,
x,
SUBSTITUTE(
a,
REPT(
"X",
x
),
"X"
)
)
)Excel solution 24 for Replace Consecutive X!, proposed by Seokho MOON:
=REGEXREPLACE(
D3:D10,
"(x)x+",
"$1",
0,
1
)Excel solution 25 for Replace Consecutive X!, proposed by Tomasz Jakóbczyk:
=HSTACK(
Data[Date],
REGEXREPLACE(
Data[ID],
"X+",
"X",
,
1
)
)Solving the challenge of Replace Consecutive X! with Python
Python solution 1 for Replace Consecutive X!, proposed by Luan Rodrigues:
import pandas as pd
from itertools import groupby
file = "CH-185 Replace consecutive X.xlsx"
df = pd.read_excel(file, usecols="C:D",skiprows=1)
df['ID'] = df['ID'].apply(lambda x: ''.join(y for y, _ in groupby(['X' if i == 'x' else i for i in x])))
print(df)Solving the challenge of Replace Consecutive X! with Python in Excel
_x000D_
Python in Excel solution 1 for Replace Consecutive X!, proposed by Alejandro Campos:
import re
[re.sub(r'[X]{2,}',Python in Excel solution 1 for Replace Consecutive X!, proposed by Alejandro Campos:
import re
[re.sub(r'[X]{2,}',