Repeat every character in the string except those characters which appear consecutively. Ex: xyyyx => xxyyyxx (y appears consecutively hence need not be repeated).
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 694
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Repeat Non-Consecutive Characters with Power Query
Power Query solution 1 for Repeat Non-Consecutive Characters, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Sol = Table.AddColumn(
Origen,
"Answer",
each
let
a = Text.ToList([Data]),
b = Table.FromColumns({a}),
c = Table.Group(b, "Column1", {"B", each List.Count([Column1])}, 0),
d = Table.AddColumn(
c,
"C",
each if [B] > 1 then Text.Repeat([Column1], [B]) else Text.Repeat([Column1], 2)
),
e = Text.Combine(d[C])
in
e
)[[Answer]]
in
SolPower Query solution 2 for Repeat Non-Consecutive Characters, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(Tabela1, "ID", each Text.ToList([Data])),
ID = Table.ExpandListColumn(Fonte, "ID"),
gpr = Table.Group(
ID,
{"Data", "ID"},
{{"tab", each if Table.RowCount(_) = 1 then List.Repeat(_[ID], 2) else _[ID]}},
0
),
res = Table.Group(gpr, {"Data"}, {{"tab", each Text.Combine(List.Combine(_[tab]))}})
in
resPower Query solution 3 for Repeat Non-Consecutive Characters, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(
Source,
"My Answer",
each [
a = [Data],
b = List.Accumulate(
{0 .. Text.Length(a) - 1},
"",
(s, c) =>
s
& [
u = Text.At(a, c),
v = try Text.At(a, c - 1) <> u otherwise true,
w = try Text.At(a, c + 1) <> u otherwise true,
x = if v and w then u & u else u
][x]
)
][b]
),
Result = Table.AddColumn(AddCol, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 4 for Repeat Non-Consecutive Characters, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = LT(S[Data], each Table.FromColumns({Text.ToList(_)})),
b = LT(a, each Table.Group(_, "Column1", {"G", each [Column1]}, 0)[G]),
c = LT(b, each LT(_, each if List.Count(_) = 1 then List.Repeat(_, 2) else _)),
d = LT(c, each Text.Combine(LT(_, Text.Combine))),
Sol = Table.FromColumns({d}, {"Answer Expected"})
in
SolPower Query solution 5 for Repeat Non-Consecutive Characters, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.AddColumn(Source, "Answer Expected", each Fun([Data])),
Fun = (x) =>
[
A = Text.ToList(x),
B = {List.FirstN(A, each _ = A{0}), List.Skip(A, each _ = A{0})},
C = if List.Count(B{0}) = 1 then Text.Repeat(B{0}{0}, 2) else Text.Combine(B{0}),
D = if B{1} = {} then "" else @Fun(Text.Combine(B{1})),
E = C & D
][E]
in
ResPower Query solution 6 for Repeat Non-Consecutive Characters, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.AddColumn(Source, "Answer Expected", Fun),
Fun = each [
A = Table.FromList(Text.ToList([Data]), each {_}, {"C1"}),
B = Table.Group(A, "C1", {"C2", each Text.Repeat([C1]{0}, List.Max({2, Table.RowCount(_)}))}, 0),
C = Text.Combine(B[C2])
][C]
in
ResPower Query solution 7 for Repeat Non-Consecutive Characters, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.TransformColumns(
Source,
{},
each Text.Combine(
Table.ReplaceValue(
Table.Group(Table.FromList(Text.ToList(_), null, {"Text"}), "Text", {"New", each _}, 0),
each [Text],
each [New],
(x, y, z) => if Table.RowCount(x) > 1 then Text.Combine(z[Text]) else y & y,
{"New"}
)[New]
)
)
in
ResultPower Query solution 8 for Repeat Non-Consecutive Characters, proposed by Antriksh Sharma:
let
Source = Table,
Fx = (str) as text =>
let
a = {null} & Text.ToList(str) & {null},
b = List.Positions(a),
c = List.Transform(
b,
(x) =>
if (try (a{x} <> a{x + 1} and a{x} <> a{x - 1}) otherwise null) = true then
List.Repeat({a{x}}, 2)
else
{a{x}}
),
d = Text.Combine(List.RemoveNulls(List.Combine(c)))
in
d,
Result = List.Transform(Source[Data], Fx)
in
ResultPower Query solution 9 for Repeat Non-Consecutive Characters, proposed by Antriksh Sharma:
let a = Text.ToList ( x ), b = List.Skip ( a ) & {null} , c = {null} & List.RemoveLastN ( a, 1 ) , d = List.Zip ( {a, b, c } ) in {d},
(x, y) => Text.Combine ( List.Transform ( y, (x)=> {x{0}, Text.Repeat ( x{0}, 2 ) } { Byte.From ( ( x{0} <> x{1} ) and ( x{0} <> x{2} ) ) } ) )
)
Power Query solution 10 for Repeat Non-Consecutive Characters, proposed by Peter Krkos:
PowerQuery solution:
= Table.AddColumn(Source, "Answer", each
Text.Combine(List.Combine(Table.AddColumn(
Table.Group(Table.FromList(Text.ToList([Data])), "Column1", {"B", Table.RowCount}, 0), "C",(x)=>
List.Repeat({x[Column1]}, List.Max({x[B], 2})))[C])),
type text)[[Answer]]
Power Query solution 12 for Repeat Non-Consecutive Characters, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.Group(
Table.Combine(
Table.Group(
Table.ExpandListColumn(Table.AddColumn(Source, "A", each Text.ToList([Data])), "A"),
{"Data", "A"},
{
"All",
each
if Table.RowCount(_) = 1 then
Table.InsertRows(_, 0, {[Data = _[Data]{0}, A = _[A]{0}]})
else
_
},
GroupKind.Local
)[All]
),
"Data",
{"Answer Expected", each Text.Combine(_[A])}
)
in
ResSolving the challenge of Repeat Non-Consecutive Characters with Excel
Excel solution 1 for Repeat Non-Consecutive Characters, proposed by Bo Rydobon 🇹🇭:
=REGEXREPLACE(A2:A9,"((.)2+)|(.)","$1$3$3")
=MAP(A2:A9,LAMBDA(w,LET(s,SEQUENCE(20),m,MID(w,s,1),CONCAT(REPT(m,1+(m<>MID(0&w,s,1))*(m<>MID(w,s+1,1)))))))Excel solution 2 for Repeat Non-Consecutive Characters, proposed by John V.:
=MAP(A2:A9,
LAMBDA(x,
LET(s,
ROW(
1:99
),
m,
MID,
i,
m(
x,
s,
1
),
CONCAT(IF((i=m(
0&x,
s,
1
))+(i=m(
x,
1+s,
1
)),
i,
i&i)))))Excel solution 3 for Repeat Non-Consecutive Characters, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
A2:A9,
"((.)2+)|(.)",
"$1$3$3"
)
=MAP(
A2:A9,
LAMBDA(
x,
LET(
c,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
CONCAT(
REPT(
c,
ISERR(
FIND(
11,
BYROW(
N(
c=TOROW(
c
)
),
CONCAT
)
)
)+1
)
)
)
)
)Excel solution 4 for Repeat Non-Consecutive Characters, proposed by Kris Jaganah:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
VSTACK(
0,
DROP(
a,
-1
)
),
c,
SCAN(
0,
a<>b,
SUM
),
CONCAT(
a&IF(
MAP(
c,
LAMBDA(
x,
SUM(
N(
x=c
)
)
)
)=1,
a,
""
)
)
)
)
)Excel solution 5 for Repeat Non-Consecutive Characters, proposed by Julian Poeltl:
=MAP(
A2:A9,
LAMBDA(
D,
LET(
S,
MID(
D,
SEQUENCE(
LEN(
D
)
),
1
),
R,
IFERROR(
S=DROP(
S,
1
),
)+DROP(
S=VSTACK(
0,
S
),
-1
),
CONCAT(
HSTACK(
S,
IF(
R,
"",
S
)
)
)
)
)
)Excel solution 6 for Repeat Non-Consecutive Characters, proposed by Timothée BLIOT:
=REGEXREPLACE(A2:A9,"((.){2,})|(.)","$1$3$3")Excel solution 7 for Repeat Non-Consecutive Characters, proposed by Duy Tùng:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
SCAN(
0,
a<>VSTACK(
0,
DROP(
a,
-1
)
),
SUM
),
CONCAT(
IF(
DROP(
FREQUENCY(
b,
b
),
-1
)=1,
REPT(
a,
2
),
a
)
)
)
)
)Excel solution 8 for Repeat Non-Consecutive Characters, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
s,
REGEXEXTRACT(
x,
"(.)1*",
1
),
CONCAT(
IF(
LEN(
s
)=1,
s&s,
s
)
)
)
)
)Excel solution 9 for Repeat Non-Consecutive Characters, proposed by Abdallah Ally:
=MAP(
A2:A9,
LAMBDA(
x,
LET(
a,
x,
b,
SEQUENCE(
LEN(
a
)
),
REDUCE(
"",
b,
LAMBDA(
x,
y,
LET(
c,
MID(
a,
y,
1
),
x&IF(
OR(
IFERROR(
c=MID(
a,
y-1,
1
),
0
),
IFERROR(
c=MID(
a,
y+1,
1
),
0
)
),
c,
c&c
)
)
)
)
)
)
)Excel solution 10 for Repeat Non-Consecutive Characters, proposed by Md. Zohurul Islam:
=MAP(
A2:A9,
LAMBDA(
z,
LET(
a,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
b,
SCAN(
1,
VSTACK(
0,
ABS(
DROP(
a,
-1
)<>DROP(
a,
1
)
)
),
SUM
),
c,
UNIQUE(
b
),
d,
DROP(
REDUCE(
"",
c,
LAMBDA(
x,
y,
LET(
p,
FILTER(
& a,
b=y
),
q,
COUNTA(
p
),
r,
IF(
q=1,
p&p,
p
),
VSTACK(
x,
r
)
)
)
),
1
),
e,
CONCAT(
d
),
e
)
)
)Excel solution 11 for Repeat Non-Consecutive Characters, proposed by Hamidi Hamid:
=LET(x,
MID(
A2:A9,
SEQUENCE(
,
20
),
1
),
y,
HSTACK(
DROP(
x,
,
1
),
IF(
SEQUENCE(
ROWS(
x
)
),
""
)
),
z,
IF(
x=y,
x,
x&x
),
w,
HSTACK(
IF(
SEQUENCE(
ROWS(
x
)
),
""
),
DROP(
z,
,
-1
)
),
q,
(LEN(
z
)=2)*(LEFT(
z,
1
)=w),
s,
BYROW(
IF(
q=1,
x,
z
),
CONCAT
),
s)Excel solution 12 for Repeat Non-Consecutive Characters, proposed by Asheesh Pahwa:
=MAP(A2:A9,LAMBDA(a,LET(m,MID(a,SEQUENCE(LEN(a)),1),u,UNIQUE(m),r,REPT(u,2),l,MAP(r,LAMBDA(x,LET(s,FIND(x,a),s))),I,IF(ISERR(l),r,""),x,XLOOKUP(m,u,I),CONCAT(IF(x="",m,x)))))Excel solution 13 for Repeat Non-Consecutive Characters, proposed by Dhaval Patel:
=LET(s,
A2,
n,
LEN(
s
),
seq,
SEQUENCE(
n
),
prev,
IF(
seq=1,
"",
MID(
s,
seq-1,
1
)
),
curr,
MID(
s,
seq,
1
),
starts,
FILTER(seq,
(seq=1)+(curr<>prev)),
blocks,
BYROW(
starts,
LAMBDA(
i,
LET(
letter,
MID(
s,
i,
1
),
nextStarts,
FILTER(
starts,
starts>i
),
len,
IFERROR(
MIN(
nextStarts
)-i,
n-i+1
),
IF(
len=1,
REPT(
letter,
2
),
MID(
s,
i,
len
)
)
)
)
),
TEXTJOIN(
"",
,
blocks
))Excel solution 14 for Repeat Non-Consecutive Characters, proposed by ferhat CK:
=MAP(
A2:A9,
LAMBDA(
v,
LET(
a,
REGEXEXTRACT(
v,
".",
1
),
c,
CHOOSECOLS,
d,
COLUMNS(
a
),
CONCAT(
MAP(
SEQUENCE(
,
d
),
LAMBDA(
x,
LET(
r,
IFS(
x=1,
x+1,
x=d,
x-1,
1,
SEQUENCE(
2,
,
x-1,
2
)
),
IF(
OR(
c(
a,
x
)=c(
a,
r
)
),
c(
a,
x
),
c(
a,
x
)&c(
a,
x
)
)
)
)
)
)
)
)
)Excel solution 15 for Repeat Non-Consecutive Characters, proposed by Meganathan Elumalai:
=MAP(
A2:A9,
LAMBDA(
c,
LET(
x,
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
y,
SCAN(
0,
VSTACK(
0,
DROP(
x,
-1
)
)<>x,
SUM
),
CONCAT(
IF(
MAP(
y,
LAMBDA(
z,
SUM(
N(
y=z
)
)>1
)
),
x,
x&x
)
)
)
)
)Excel solution 16 for Repeat Non-Consecutive Characters, proposed by JvdV –:
=REGEXREPLACE(A2:A9,"(.)1+|(.)","$0$2")Excel solution 17 for Repeat Non-Consecutive Characters, proposed by Guillermo Arroyo:
=MAP(
A2:A9,
LAMBDA(
a,
TRIM(
REDUCE(
"",
MID(
a,
SEQUENCE(
LEN(
a
)+1
),
1
),
LAMBDA(
i,
j,
IF(
OR(
j=RIGHT(
i
),
RIGHT(
i,
2
)=REPT(
RIGHT(
i,
1
),
2
)
),
i&j,
i&RIGHT(
i,
1
)&j
)
)
)
)
)
)
or
=REGEXREPLACE(
A2:A9,
"((.)2+)|(.)",
"$1$3$3"
)Excel solution 18 for Repeat Non-Consecutive Characters, proposed by Maciej Kopczyński:
=LET(
arr,
BYROW(
tblStart[Data],
LAMBDA(
row,
REDUCE(
"",
MID(
row,
SEQUENCE(
LEN(
row
),
1,
1,
1
),
1
),
LAMBDA(
state,
curr,
IF(
RIGHT(
state,
1
)=curr,
state & curr,
state & ":" & curr
)
)
)
)
),
result,
MAP(
arr,
LAMBDA(
row,
TEXTJOIN(
"",
,
MAP(
TEXTSPLIT(
row,
":",
,
TRUE
),
LAMBDA(
cell,
IF(
LEN(
cell
)=1,
REPT(
cell,
2
),
cell
)
)
)
)
)
),
result
)Excel solution 19 for Repeat Non-Consecutive Characters, proposed by Erdit Qendro:
=DROP(REDUCE("",A2:A9,LAMBDA(a,v,
VSTACK(a,LET(rg,REGEXEXTRACT(v,"w",1),
fwrg,HSTACK(DROP(rg,,1),0),rvrg,HSTACK(0,DROP(rg,,-1)),
CONCAT(IF((rg=fwrg)+(rg=rvrg),rg,REPT(rg,2))))))),1)Excel solution 20 for Repeat Non-Consecutive Characters, proposed by Fredson Alves Pinho:
=REGEXREPLACE(A2:A9,CONCAT("("&CHAR(SEQUENCE(26,,97))&"{2,})|")&"(.)","$0$27")Solving the challenge of Repeat Non-Consecutive Characters with Python
Python solution 1 for Repeat Non-Consecutive Characters, proposed by Konrad Gryczan, PhD:
import pandas as pd
from itertools import groupby
path = "694 Repeat Characters Except Consecutives.xlsx"
input = pd.read_excel(path, usecols="A", nrows=9)
test = pd.read_excel(path, usecols="B", nrows=9)
def double_single_chars(s):
return ''.join(g*2 if len(g)==1 else g for g in (''.join(list(grp)) for _, grp in groupby(s)))
input["Transformed"] = input["Data"].apply(double_single_chars)
print(input['Transformed'] == test['Answer Expected'])
Python solution 2 for Repeat Non-Consecutive Characters, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = r"Excel_Challenge_694 - Repeat Characters Except Consecutives.xlsx"
df = pd.read_excel(file, usecols='A')
df = df.assign(ID=df['Data'].apply(list)).explode('ID', ignore_index=True)
df['grp'] = df.groupby('Data')['ID'].transform(lambda x: (x != x.shift()).cumsum())
df = df.groupby(['Data','ID','grp'],sort=False).size().reset_index(name='count')
df['ID'] = np.where(df['count'] == 1,df['ID']*2,df['ID']*df['count'] )
df = df.groupby(['Data'],sort=False)['ID'].agg(''.join).reset_index()[['ID']]
print(df)
Python solution 3 for Repeat Non-Consecutive Characters, proposed by Claudiu B.:
import pandas as pd
df = pd.read_excel(r"strings.xlsx")
def rep_check(Data):
count=1
cnt_list = []
for i in range(1, len(Data)):
if Data[i] == Data[i-1]:
count+=1
else:
cnt_list.append(count)
count=1
cnt_list.append(count)
cnt_list_final = [ 2 if i==1 else i for i in cnt_list ]
str_comp = [Data[0]]
for i in range(1, len(Data)):
if Data[i] != Data[i-1]:
str_comp.append(Data[i])
temp = [i*j for i,j in zip(str_comp, cnt_list_final)]
output = ''.join(temp)
return output
df['new_Data'] = df['Data'].apply(rep_check)
df
Output:
Data new_Data
0 jqum jjqquumm
1 beeaao bbeeaaoo
2 xyzxyz xxyyzzxxyyzz
3 wreeemccc wwrreeemmccc
4 caaaaaaac ccaaaaaaacc
5 zzzzzyttttt zzzzzyyttttt
6 wwddeeqq wwddeeqq
7 qwwwwwrty qqwwwwwrrttyy
Solving the challenge of Repeat Non-Consecutive Characters with Python in Excel
Python in Excel solution 1 for Repeat Non-Consecutive Characters, proposed by Alejandro Campos:
def r(s):return''.join(c*2 if(i==0 or c!=s[i-1])and(i==len(s)-1 or c!=s[i+1])else c for i,c in enumerate(s))
data=xl("A2:A9")[0]
df=pd.DataFrame({"Original":data,"Transformed":[r(s)for s in data]})
Python in Excel solution 2 for Repeat Non-Consecutive Characters, proposed by Antriksh Sharma:
df = xl("A1:A9", headers= True)
def repeat_characters(string):
text = string
result = []
for i in range(0, len(text)):
prev = text[i - 1] if i > 0 else ''
curr = text[i]
next_ = text[i + 1] if i < len(text) - 1 else ''
n = (curr != prev) * (curr != next_)
result.append(curr * 2) if n == 1 else result.append(curr)
return ''.join(result)
df['Ans'] = df['Data'].apply(repeat_characters)
df['Ans']
Solving the challenge of Repeat Non-Consecutive Characters with R
R solution 1 for Repeat Non-Consecutive Characters, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/694 Repeat Characters Except Consecutives.xlsx"
input = read_excel(path, range = "A1:A9")
test = read_excel(path, range = "B1:B9")
result = input %>%
mutate(rn = row_number()) %>%
separate_rows(Data, sep = "") %>%
filter(Data != "") %>%
mutate(rn2 = consecutive_id(Data), .by = c(rn)) %>%
mutate(rn3 = n(), .by = c(rn, rn2)) %>%
mutate(Data = ifelse(rn3 == 1, paste0(Data, Data), Data)) %>%
summarise(Data = paste0(Data, collapse = ""), .by = c(rn))
&&