Extract the string before any alphabet is repeated. Hence, in case of “Apple”, the answer is “Ap”. In case of “Debt”, no alphabet is repeated. Hence answer is “Debt”
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 333
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract string before any letter repeats with Power Query
Power Query solution 1 for Extract string before any letter repeats, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
s = Text.ToList([String]),
f = List.FirstN
in
Text.Combine(
List.Transform(
f(List.Positions(s), each List.IsDistinct(f(s, _ + 1), Comparer.OrdinalIgnoreCase)),
each s{_}
)
)
)
in
SPower Query solution 2 for Extract string before any letter repeats, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content][String],
S = List.Transform(
Source,
each List.Last(
List.Generate(
() => 1,
(g) =>
g
<= Text.Length(_)
and List.Count(
Text.PositionOf(Text.Start(_, g), Text.At(_, g - 1), 2, Comparer.OrdinalIgnoreCase)
)
= 1,
(g) => g + 1,
(g) => Text.Start(_, g)
)
)
)
in
SPower Query solution 3 for Extract string before any letter repeats, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
F = (l) =>
if List.IsDistinct(l, Comparer.OrdinalIgnoreCase) then
Text.Combine(l)
else
@F(List.RemoveLastN(l))
in
F(Text.ToList([String]))
)
in
SPower Query solution 4 for Extract string before any letter repeats, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each Text.Combine(
List.Last(
List.Select(
List.TransformMany(
{1 .. Text.Length([String])},
(x) => {List.FirstN(Text.ToList([String]), x)},
(x, y) => y
),
each List.IsDistinct(_, Comparer.OrdinalIgnoreCase)
)
)
)
)
in
SPower Query solution 5 for Extract string before any letter repeats, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
P = List.Transform(
{"A" .. "Z"},
(f) => Text.PositionOf([String], f, - 1, Comparer.OrdinalIgnoreCase)
),
S = List.Select(P, (f) => List.Count(f) > 1),
C = (S{0}? ?? {0, Text.Length([String])}){1},
R = Text.Start([String], C)
][R]
)
in
ReturnPower Query solution 6 for Extract string before any letter repeats, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Fx = (T, N) =>
let
a = T,
b = Text.Range(Text.Lower(a), 0, N),
c = List.Transform(Text.ToList(b), (x) => Text.Length(Text.Select(b, x)))
in
if List.Max(c) = 1 then @Fx(a, N + 1) else Text.Range(a, 0, N - 1),
Sol = Table.AddColumn(Source, "Answer", each try Fx([String], 1) otherwise [String])[[Answer]]
in
SolPower Query solution 7 for Extract string before any letter repeats, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Answer", (x)=>
let
//texto en minusculas
a = Text.Lower(x[String]),
//se separa el texto, letra a letra hasta completar el texto
b = List.Transform({1..Text.Length(a)}, each Text.Range(a, 0, _)),
//se cuentan las letras del cada texto obtenido en el paso anterior, y se calcula su max. repetición.
c = List.Transform(b, each List.Max(List.Transform(Text.ToList(_), (y)=> Text.Length(Text.Select(_, y))))),
//se seleccionan los textos sin repeticiones (1) y se cuenta el número de letras sin repeticiones
d = List.Count(List.Select(c, each _=1)),
//del texto original se obtiene aquellas letras sin repetición de acuerdo al número de letras encontrado en el paso anterior.
e = Text.Range(x[String], 0, d)
in e)[[Answer]]
in
Sol
Show translation
Show translation of this commentPower Query solution 8 for Extract string before any letter repeats, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
x = List.Select(
List.Transform(
{0 .. Text.Length([String])},
(x) =>
[
a = Text.Middle([String], 0, x),
b = {a} & {List.IsDistinct(Text.ToList(a), Comparer.OrdinalIgnoreCase)}
][b]
),
each _{1} = true
),
y = List.Last(List.Transform(x, each _{0}))
][y]
)
in
resPower Query solution 9 for Extract string before any letter repeats, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddAnswer = Table.AddColumn(
Source,
"Answer",
each [
Letters = Text.ToList(Text.Lower([String])),
Range = {1 .. List.Count(Letters)},
RangeDistinct = List.Select(Range, (N) => List.IsDistinct(List.FirstN(Letters, N))),
Result = Text.Start([String], List.Max(RangeDistinct))
][Result]
)
in
AddAnswerPower Query solution 10 for Extract string before any letter repeats, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddAnswer = Table.AddColumn(
Source,
"Answer",
each [
a = Text.ToList(Text.Lower([String])),
b = List.Transform(List.Positions(a), each _ + 1),
c = List.Count(a),
d = Table.FromColumns({a, b}),
e = Table.AddColumn(
d,
"AllUnique",
each
if Table.IsDistinct(Table.SelectColumns(Table.FirstN(d, [Column2]), {"Column1"})) then
1
else
0
),
f = Table.ToColumns(e){2},
g = List.Sum(List.Transform(f, each Number.From(_))),
h = Text.Start([String], g)
][h]
)
in
AddAnswerPower Query solution 11 for Extract string before any letter repeats, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
Fx = (x) =>
let
a = x,
b = Text.ToList(Text.Lower(a)),
c = List.Generate(
() => [i = 1],
each [i] <= Text.Length(a),
each [i = [i] + 1],
each List.Range(b, 0, [i])
),
d = List.Last(List.Select(c, each List.IsDistinct(_))),
e = Text.Proper(Text.Combine(d, ""))
in
e,
Sol = Table.AddColumn(Origen, "Answer Expected", each Fx([String]))
in
SolSolving the challenge of Extract string before any letter repeats with Excel
Excel solution 1 for Extract string before any letter repeats, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
SEQUENCE(
LEN(
a
)
),
LEFT(
a,
MIN(
SEARCH(
MID(
a,
s,
1
),
a&a,
s+1
)
)-1
)
)
)
)Excel solution 2 for Extract string before any letter repeats, proposed by Rick Rothstein:
=MAP(A2:A10,LAMBDA(w,LET(a,UPPER(w),s,SEQUENCE(LEN(a)),LEFT(w,MAX(MAP(s,LAMBDA(x,IF(ISERROR(MODE(TAKE(CODE(MID(a,s,1)),x))),x))))))))Excel solution 3 for Extract string before any letter repeats, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(m,MID(x,ROW(1:15),1),c,XMATCH(m,m),LEFT(x,MATCH(,FREQUENCY(c,c),)-1))))
✅=MAP(A2:A10,LAMBDA(x,LET(m,MID(x,ROW(1:20),1),LEFT(x,MATCH(,-IFNA(m=UNIQUE(m),))-1))))Excel solution 4 for Extract string before any letter repeats, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(a,LET(d,MID(a,SEQUENCE(20),1),
u,UNIQUE(d),CONCAT(IFNA(IF(d=u,u,""),"")))))Excel solution 5 for Extract string before any letter repeats, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
MID(
a,
SEQUENCE(
20
),
1
),
LEFT(
a,
SUM(
IFNA(
N(
d=UNIQUE(
d
)
),
)
)
)
)
)
)Excel solution 6 for Extract string before any letter repeats, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
MID(
a,
SEQUENCE(
20
),
1
),
LEFT(
a,
SUM(
TOCOL(
N(
d=UNIQUE(
d
)
),
2
)
)
)
)
)
)Excel solution 7 for Extract string before any letter repeats, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(x,
LET(a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
UNIQUE(
a
),
CONCAT(FILTER(a,
IFNA(--(a=b),
0))))))Excel solution 8 for Extract string before any letter repeats, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
N,
LET(
L,
LEN(
N
),
SP,
MID(
N,
SEQUENCE(
1,
L
),
1
),
SLOW,
LOWER(
SCAN(
"",
SP,
LAMBDA(
A,
B,
A&B
)
)
),
LS,
LEN(
SLOW
)-LEN(
SUBSTITUTE(
SLOW,
RIGHT(
SLOW,
1
),
""
)
),
LSL,
XMATCH(
2,
LS,
1
),
IFERROR(
PROPER(
CHOOSECOLS(
SLOW,
LSL-1
)
),
N
)
)
)
)Excel solution 9 for Extract string before any letter repeats, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z,LET(A,SEQUENCE(LEN(z)),CONCAT(MAP(A,LAMBDA(x,IF(SUM(TAKE(--MAP(A,LAMBDA(y,SEARCH(MID(z,y,1),z)Excel solution 10 for Extract string before any letter repeats, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
UPPER(
a
),
CONCAT(
FILTER(
a,
IFERROR(
b=UNIQUE(
b
),
0
)
)
)
)
)
)Excel solution 11 for Extract string before any letter repeats, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
_ts,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
_xm,
XMATCH(
_ts,
UNIQUE(
_ts
)
),
CONCAT(
FILTER(
_ts,
_xm = SEQUENCE(
ROWS(
_xm
)
)
)
)
)
)
)Excel solution 12 for Extract string before any letter repeats, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
F,
LAMBDA(
F,
x,
y,
LET(
i,
LEFT(
x,
&y
),
IF(
ISNUMBER(
SEARCH(
MID(
x,
y+1,
1
),
i
)
),
i,
F(
F,
x,
y+1
)
)
)
),
F(
F,
a,
1
)
)
)
)
2. BYROW with direct function without lambda
=LET(
p,
A2:A10,
s,
SEQUENCE(
,
20
),
LEFT(
p,
BYROW(
IF(
ISNUMBER(
SEARCH(
MID(
p,
s+1,
1
),
LEFT(
p,
s
)
)
),
s,
""
),
MIN
)
)
)Excel solution 13 for Extract string before any letter repeats, proposed by Abdallah Ally:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
x,
b,
UNIQUE(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
LEFT(
a,
MIN(
IFERROR(
SEARCH(
b,
a,
SEARCH(
b,
a
)+1
),
LEN(
a
)+1
)
)-1
)
)
)
)Excel solution 14 for Extract string before any letter repeats, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
LAMBDA(
r,
TAKE(
TOCOL(
MAP(
SEQUENCE(
LEN(
r
)
),
LAMBDA(
v,
LET(
m,
MID(
r,
SEQUENCE(
v
),
1
),
n,
UNIQUE(
m
),
IF(
AND(
m=n
),
CONCAT(
n
)
)
)
)
),
3
),
-1
)
)
)Excel solution 15 for Extract string before any letter repeats, proposed by Thang Van:
=MAP(
A2:A10,
LAMBDA(
_str,
LET(
_s,
_str,
_seq,
SEQUENCE(
LEN(
_s
),
,
1
),
_r,
MAP(
_seq,
LAMBDA(
_each,
IF(
SEARCH(
MID(
_s,
_each,
1
),
_s
)<_each,
LEFT(
_s,
_each-1
),
_s
)
)
),
UNIQUE(
FILTER(
_r,
LEN(
_r
)=MIN(
LEN(
_r
)
)
)
)
)
)
)Excel solution 16 for Extract string before any letter repeats, proposed by Charles Roldan:
=LAMBDA(
f,
LAMBDA(
x,
MAP(
x,
LAMBDA(
x,
f(
x
)
)
)
)
)(LAMBDA(
f,
f(
f
)
)(LAMBDA(f,
LAMBDA(x,
[y],
LET(l,
LEFT(
x
),
IF(AND(
LEN(
x
),
ISERROR(
SEARCH(
l,
y
)
)
),
f(
f
)(REPLACE(
x,
1,
1,
),
y & l),
y)))))
)(A2:A10)Excel solution 17 for Extract string before any letter repeats, proposed by Owen Price:
=BYROW(
A2:A10,
LAMBDA(
d,
LET(
s,
SEQUENCE(
LEN(
d
)
),
c,
MID(
d,
s,
1
),
TEXTBEFORE(
CONCAT(
IF(
SEARCH(
c,
d
) = s,
c,
" "
)
),
" ",
,
,
,
d
)
)
)
)Excel solution 18 for Extract string before any letter repeats, proposed by Ziad A.:
=LET(
R,
LAMBDA(
R,
i,
LET(
a,
LEFT(
A2,
i
),
IF(
REGEXMATCH(
a,
"(?i)"&MID(
A2,
i+1,
1
)
),
a,
R(
R,
i+1
)
)
)
),
R(
R,
)
)
Scans the string LTR using recursion.
IF the next character is contained in the accumulated string (base case) THEN Return the accumulated string,
ELSE insert the next character in the accumulated string (recursive case)Excel solution 19 for Extract string before any letter repeats, proposed by Giorgi Goderdzishvili:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
_wr,
z,
_cr,
LOWER(
MID(
_wr,
SEQUENCE(
,
LEN(
_wr
)
),
1
)
),
_un,
UNIQUE(
_cr,
TRUE
),
_mp,
MAP(
_un,
LAMBDA(
w,
XMATCH(
2,
SCAN(
0,
IF(
_cr=w,
1,
0
),
LAMBDA(
a,
v,
a+v
)
),
0
)
)
),
_fin,
MID(
_wr,
1,
MIN(
TOCOL(
_mp,
3
)
)-1
),
IFERROR(
_fin,
_wr
)
)
)
)Excel solution 20 for Extract string before any letter repeats, proposed by Edwin Tisnado:
=MAP(A2:A10,LAMBDA(t,LET(a,MID(t,SEQUENCE(LEN(t)),1),l,IFERROR(TAKE(TOCOL(MAP(UNIQUE(a),LAMBDA(x,IF(SUM(--(x=a))>1,x,NA()))),2),1),5),TEXTBEFORE(t,l,2,1,1,t))))Excel solution 21 for Extract string before any letter repeats, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,
LAMBDA(a,
LET(b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(FILTER(b,
IFNA(--(b=UNIQUE(
b
)),
0))))))Excel solution 22 for Extract string before any letter repeats, proposed by Anup Kumar:
=MAP(
A2:A10,
LAMBDA(
wd,
LET(
sq,
SEQUENCE(
LEN(
wd
)
),
wr,
MID(
wd,
sq,
1
),
pt,
XLOOKUP(
wr,
wr,
sq
)=sq,
tf,
SCAN(
TRUE,
pt,
LAMBDA(
x,
y,
AND(
x=y,
y
)
)
),
CONCAT(
FILTER(
wr,
tf
)
)
)
)
)Excel solution 23 for Extract string before any letter repeats, proposed by Hazem Hassan:
=MAP(A2:A10,LAMBDA(X,LET(a,MID(X,SEQUENCE(LEN(X)),1),LEFT(X,SUM(1*TOCOL(a=UNIQUE(a),3))))))Excel solution 24 for Extract string before any letter repeats, proposed by Viswanathan M B:
=MAP(A2:A10,
LAMBDA(txt,
LET(
Sqn,
SEQUENCE(
LEN(
txt
)
),
TSqn,
TRANSPOSE(
Sqn
),
LEFT(txt,
MATCH(0,
BYROW((MID(
txt,
Sqn,
1
)=MID(
txt,
TSqn,
1
))*(Sqn>TSqn),
SUM))))))Excel solution 25 for Extract string before any letter repeats, proposed by Talia Cao, CPA:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
l,
LEN(
t
),
c,
MID(
t,
SEQUENCE(
l
),
1
),
CONCAT(
IF(
c = EXPAND(
UNIQUE(
c
),
l,
,
""
),
c,
""
)
)
)
)
)Solving the challenge of Extract string before any letter repeats with Python in Excel
Python in Excel solution 1 for Extract string before any letter repeats, proposed by JvdV -:
=REDUCE(A2:A10,ROW(1:99),LAMBDA(x,y,IF(SEARCH(MID(x,y,1),x)Python in Excel solution 2 for Extract string before any letter repeats, proposed by Owen Price:
Here's one way of doing it in Python:
Get A2:A10 as a series. Call it s.
- define an empty string r
- for each character c in w:
s = xl("A2:A10")[0]
def fn(w):
r = ""
for c in w:
if c.lower() in r.lower():
break
r += c
return r
s.apply(fn).values
Solving the challenge of Extract string before any letter repeats with R
R solution 1 for Extract string before any letter repeats, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Extract String Before a Repeated Character.xlsx", range = "A1:A10")
test = read_excel("Extract String Before a Repeated Character.xlsx", range = "B1:B10")
result = input %>%
mutate(lett = str_split(str_to_lower(String), pattern = ""),
reps = map(lett, ~ duplicated(.x)),
first_reps = map_int(reps, ~ which(.x)[1]),
`Answer Expected` = ifelse(!is.na(first_reps),
str_sub(String, 1, first_reps-1),
String)) %>%
select(`Answer Expected`)
Solving the challenge of Extract string before any letter repeats with Excel VBA
Excel VBA solution 1 for Extract string before any letter repeats, proposed by Nicolas Micot:
Function f_challenge333(ByVal texte As String) As String
Dim lettre As String, resultat As String
For i = 1 To Len(texte)
lettre = Mid(texte, i, 1)
If Not InStr(1, resultat, lettre, vbTextCompare) > 0 Then
resultat = resultat & lettre
Else
Exit For
End If
Next i
f_challenge333 = resultat
End Function
Excel VBA solution 2 for Extract string before any letter repeats, proposed by Harshit Agrawal:
Function ExtractResults(inputString As String) As String
Dim i As Integer
Dim charCount As Collection
Set charCount = New Collection
For i = 1 To Len(inputString)
On Error Resume Next
charCount.Add 1, CStr(Mid(UCase(inputString), i, 1))
On Error GoTo 0
Next i
Dim result As String
result = ""
For i = 1 To Len(inputString)
If charCount(CStr(Mid(UCase(input&String), i, 1))) > 1 Then
Exit For
Else
If InStr(result, Mid(inputString, i, 1)) = 0 Then
result = result & Mid(inputString, i, 1)
End If
End If
Next i
ExtractResults = result
End Function
&