— CAN YOU SOLVE THIS - EXCEL CHALLENGE 179 —
(Solutions in any language are also welcome for Excel Challenges)
If a character appears consecutively, then remove all consecutive characters other than first.
Ex - xxxyxyyz
x appears 3 times consecutively. Hence first x will be retained and next 2 will be removed.
yy appears 2 times consecutively, hence only one y will be retained.
Answer = xyxyz
Post answers in Comment.
(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)
Download Practice File - https://lnkd.in/dxEs2tXT
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI LinkedIn Post
Removing consecutive characters w/ #powerquery. #bitanbit #powerbi
let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.TransformRows(Source, each Text.Combine(List.Accumulate(Text.ToList([String]), {}, (s, d) => if Comparer.OrdinalIgnoreCase(List.Last(s)??"", d) = 0 then s else s & {d})))
in
Solution
It may be one of the ways to do it in Power Query.
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
Text = [String],
Lower = Text.Lower(Text),
Length = Text.Length(Text) - 1,
Calc = List.Transform(
{0 .. Length},
(f) =>
if Text.At(Lower, f) = Text.At(" " & Lower, f) then null else Text.At(Text, f)
),
Final = Text.Combine(Calc)
][Final]
)
in
Return
Here is one way out in Excel.
=MAP(
A2:A6,
LAMBDA(a,
LET(
\sq, SEQUENCE(LEN(a)),
\s, MID(a, \sq, 1),
\c, IF(\s = MID(" " & a, \sq, 1), "", \s),
\r, CONCAT(\c),
\r
)
)
)
Here:
sq = Sequence
s = Split
c = Calculation for removing repeated characters
r = Return
My solution :
=MAP(A2:A6,LAMBDA(a,CONCAT(IF(MID(a,SEQUENCE(LEN(a)),1)<>MID(a,SEQUENCE(LEN(a))+1,1),MID(a,SEQUENCE(LEN(a)),1),""))))
But for it lowercase the Capital E in Excel
(as it takes that last letter of a repetition)
hi everyone, my proposal solution:
=BYROW(A2:A6; LAMBDA(r; LET(_s; SEQUENCE(LEN(r));_m; MID(r;_s;1);_n; MID(LOWER(r);1;_s); CONCAT(FILTER(_m; ISERROR(SEARCH("|| "; SUSBTITUTE(_n;_m;"|")&" ")))))))
This works in hashtag#googlesheets :
=ArrayFormula(
REDUCE(A2:A6, CHAR(SEQUENCE(26,1,65)),
LAMBDA(string, chr,
REGEXREPLACE(string, "(?i)("&chr&"){2,}", "$1")
)))
I wanted to use a bit of VBA for this one:
Function f_challenge(texte As String) As String
Dim resultat As String
For i = 1 To Len(texte)
If Not LCase(Mid(texte, i, 1)) = LCase(Right(resultat, 1)) Then
resultat = resultat & Mid(texte, i, 1)
End If
Next i
f_challenge = resultat
End Function
EN: