Remove the trailing and leading underscore characters. In between, underscore character will not be removed. Hence if String =”__E_xce__l____”, then answer would be “E_xce__l”
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 119
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Trim Underscores at Ends with Power Query
Power Query solution 1 for Trim Underscores at Ends, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Trimm = Table.TransformColumns(Source, {{"String", each Text.Trim(_, "_")}})
in
TrimmPower Query solution 2 for Trim Underscores at Ends, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Output = Table.AddColumn(Source, "Output", each Text.Trim([String], "_"))
in
OutputPower Query solution 3 for Trim Underscores at Ends, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Remove = Table.AddColumn(Source, "New", each Text.TrimStart([String], "_")),
Sol = Table.AddColumn(Remove, "Result", each Text.TrimEnd([New], "_"))[[Result]]
in
SolPower Query solution 4 for Trim Underscores at Ends, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
sub1 = Table.ReplaceValue(Fonte, "_", " ", Replacer.ReplaceText, {"String"}),
trim = Table.TransformColumns(sub1, {{"String", Text.Trim, type text}}),
sub2 = Table.ReplaceValue(trim, " ", "_", Replacer.ReplaceText, {"String"})
in
sub2Power Query solution 5 for Trim Underscores at Ends, proposed by Brian Julius:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PYrBCkBAFEW/xWBnIVY+wEaRlc2kkzRFCT3zFv4eTdmdc8+11gwOdr0wY2YNcT2JX4I0eq7eSQiQJC1OZlC509fDqaCk39Qf/NMHHY4Tj7LzYgh5RTvJFX06Pg==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [String = _t]
),
Dupe = Table.DuplicateColumn(Source, "String", "Answer"),
RunRegexR = R.Execute(
"library(stringr)#(lf)df <- dataset#(lf)df$Answer <- str_replace_all(df$Answer, ""^(?:_+)|(?:_+)$"", """")",
[dataset = Dupe]
),
Result = RunRegexR{[Name = "df"]}[Value]
in
ResultPower Query solution 6 for Trim Underscores at Ends, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "Trim"]}[Content],
Trimmed = Table.TransformColumns(Source, {{"String", each Text.Trim(_, "_")}})
in
TrimmedPower Query solution 7 for Trim Underscores at Ends, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ConvertToList = Table.TransformColumns(Source, {"String", Text.ToList}),
TableCheck = Table.AddColumn(
ConvertToList,
"Check",
each Table.FromRows(List.Zip({[String], List.Transform([String], each Text.Contains(_, "_"))}))
),
RemoveTrim = Table.AddColumn(
TableCheck,
"Answer",
each Text.Combine(
Table.RemoveColumns(
Table.RemoveLastN(Table.Skip([Check], each [Column2] = true), each [Column2] = true),
"Column2"
)[Column1]
)
),
Result = Table.SelectColumns(RemoveTrim, {"Answer"})
in
ResultPower Query solution 8 for Trim Underscores at Ends, proposed by Jan Willem Van Holst:
let
FXBegin = (inputText) =>
if Text.Range(inputText, 0, 1) <> "_" then
inputText
else
@FXBegin(Text.RemoveRange(inputText, 0)),
FXEnd = (inputText) =>
if Text.Range(inputText, Text.Length(inputText) - 1, 1) <> "_" then
inputText
else
@FXEnd(Text.RemoveRange(inputText, Text.Length(inputText) - 1)),
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"PYrBCkBAFEW/xWBnIVY+wEaRlc2kkzRFCT3zFv4eTdmdc8+11gwOdr0wY2YNcT2JX4I0eq7eSQiQJC1OZlC509fDqaCk39Qf/NMHHY4Tj7LzYgh5RTvJFX06Pg==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [String = _t]
),
#"Added Custom" = Table.AddColumn(Source, "Answer", each FXBegin(FXEnd([String])))
in
#"Added Custom"Power Query solution 9 for Trim Underscores at Ends, proposed by Debbie Compton:
= Table.TransformColumns(#"Duplicated Column",{{"String - Copy", each Text.Trim(_,{"_"}), type text}})Solving the challenge of Trim Underscores at Ends with Excel
Excel solution 1 for Trim Underscores at Ends, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
b,
N(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)<>"_"
),
MID(
LEFT(
a,
XMATCH(
1,
b,
,
-1
)
),
XMATCH(
1,
b
),
99
)
)
)
)Excel solution 2 for Trim Underscores at Ends, proposed by Bo Rydobon 🇹🇭:
=REDUCE(A2:A8,
SEQUENCE(
20
),
LAMBDA(a,
z,
LET(l,
LEFT(
a
)="_",
MID(a,
1+l,
LEN(
a
)-l-(RIGHT(
a
)="_")))))Excel solution 3 for Trim Underscores at Ends, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
b,
TEXTSPLIT(
a,
,
"_"
),
TEXTJOIN(
"_",
0,
DROP(
TAKE(
b,
XMATCH(
"?*",
b,
2,
-1
)
),
MATCH(
"?*",
b,
)-1
)
)
)
)
)Excel solution 4 for Trim Underscores at Ends, proposed by Rick Rothstein:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
r,
SEQUENCE(
LEN(
x
)
),
a,
IF(
MID(
x,
r,
1
)<>"_",
r
),
m,
MIN(
a
),
MID(
x,
m,
MAX(
a
)-m+1
)
)
)
)Excel solution 5 for Trim Underscores at Ends, proposed by John V.:
=MAP(A2:A8,
LAMBDA(x,
LET(f,
LAMBDA(b,
t,
MATCH(b,
1/(MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)<>"_"),
t)),
MID(
x,
f(
1,
0
),
1+f(
2,
1
)-f(
1,
0
)
))))
✅=MAP(
A2:A8,
LAMBDA(
x,
LET(
s,
SEQUENCE(
LEN(
x
)
),
n,
IF(
MID(
x,
s,
1
)<>"_",
s
),
m,
MIN(
n
),
MID(
x,
m,
1+MAX(
n
)-m
)
)
)
)Excel solution 6 for Trim Underscores at Ends, proposed by محمد حلمي:
=LET(
L,
LAMBDA(
x,
TEXTAFTER(
x,
MID(
REPT(
"_",
10
),
1,
ROW(
1:10
)
)
)
),
LL,
LAMBDA(
x,
MAP(
x,
LAMBDA(
a,
CONCAT(
MID(
a,
LEN(
a
)-SEQUENCE(
LEN(
a
)
)+1,
1
)
)
)
)
),
LL(
L(
LL(
L(
"_"&A2:A8&"_"
)
)
)
)
)
Unsuccessful attempts
=SUBSTITUTE(
TRIM(
SUBSTITUTE(
A2:A8,
"_",
" "
)
),
" ",
"_"
)
=TEXTAFTER(
"_"&A2:A8&"_",
MID(
REPT(
"_",
10
),
1,
ROW(
1:10
)
)
)
I think TEXTAFTER much better than TEXTBEFORE
I hope that the result will be made of these
=LET(
a,
MID(
REPT(
"_",
10
),
1,
ROW(
1:10
)
),
TEXTBEFORE(
TEXTAFTER(
"_"&A2:A8&"_",
a
),
a,
-1
)
)Excel solution 7 for Trim Underscores at Ends, proposed by محمد حلمي:
=REDUCE(
A2:A8,
SEQUENCE(
20
),
LAMBDA(
a,
v,
LET(
r,
IF(
LEFT(
a
)="_",
MID(
a,
2,
99
),
a
),
IF(
RIGHT(
r
)="_",
MID(
r,
1,
LEN(
r
)-1
),
r
)
)
)
)Excel solution 8 for Trim Underscores at Ends, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
A2:A8,
"^_+|_+$",
)Excel solution 9 for Trim Underscores at Ends, proposed by Kris Jaganah:
=MAP(A2:A8,
LAMBDA(x,
LET(b,
LEN(
x
),
c,
SEQUENCE(
b
),
d,
MID(
x,
c,
1
),
e,
MAX(
IF(
d<>"_",
c,
d
)
),
f,
MIN(
IF(
d<>"_",
c,
d
)
),
CONCAT(FILTER(d,
(c>=f)*(c<=e))))))Excel solution 10 for Trim Underscores at Ends, proposed by Julian Poeltl:
=MAP(A2:A8,
LAMBDA(S,
LET(SP,
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
),
U,
--(SP<>"_"),
CONCAT(DROP(DROP(SP,
-(ROWS(
U
)-XMATCH(
1,
U,
,
-1
))),
XMATCH(
1,
U
)-1)))))Excel solution 11 for Trim Underscores at Ends, proposed by Timothée BLIOT:
=MAP(
A2:A8,
LAMBDA(
x,
CONCAT(
LET(
A,
SEQUENCE(
LEN(
x
)
),
B,
MID(
x,
A,
1
),
D,
IF(
B="_",
B,
A
),
MID(
x,
SEQUENCE(
MAX(
D
)-MIN(
D
)+1,
,
MIN(
D
)
),
1
)
)
)
)
)Excel solution 12 for Trim Underscores at Ends, proposed by Hussein SATOUR:
=MAP(A2:A8,
LAMBDA(x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
XMATCH(1,
(a <> "_") * 1),
c,
XMATCH(1,
(a <> "_") * 1,
,
-1),
MID(
x,
b,
c - b + 1
))))Excel solution 13 for Trim Underscores at Ends, proposed by Charles Roldan:
=MAP(
A2:A8,
LAMBDA(
String,
LET(
Array,
MID(
String,
SEQUENCE(
LEN(
String
)
),
1
),
_END,
LAMBDA(
SearchMode,
XMATCH(
FALSE,
Array="_",
,
SearchMode
)
),
CONCAT(
DROP(
TAKE(
Array,
_END(
-1
)
),
_END(
1
) - 1
)
)
)
)
)Excel solution 14 for Trim Underscores at Ends, proposed by Jaroslaw Kujawa:
=LAMBDA(a;
LET(l;
XMATCH(1;
1*(MID(
a;
SEQUENCE(
;
LEN(
a
)
);
1
)_));
r;
XMATCH(1;
1*(MID(
a;
SEQUENCE(
;
LEN(
a
);
LEN(
a
);
-1
);
1
)_));
MID(
a;
l;
LEN(
a
)-l-r+2
)))(A2)Excel solution 15 for Trim Underscores at Ends, proposed by Stefan Olsson:
=arrayformula(
REGEXEXTRACT(
A2:A8,
"^_*(.*?)_*$"
)
)Excel solution 16 for Trim Underscores at Ends, proposed by Guillermo Arroyo:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
f,
LAMBDA(
s,
t,
LET(
l,
LEN(
s
),
i,
CHOOSE(
t,
1,
l
),
p,
CHOOSE(
t,
1,
-1
),
REDUCE(
"",
MID(
s,
SEQUENCE(
,
l,
i,
p
),
1
),
LAMBDA(
b,
c,
IF(
AND(
b="",
c="_"
),
"",
CHOOSE(
t,
b&c,
c&b
)
)
)
)
)
),
f(
f(
a,
1
),
2
)
)
)
)Excel solution 17 for Trim Underscores at Ends, proposed by Daniel Garzia:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
m,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)<>"_",
f,
XMATCH(
TRUE,
m
),
MID(
x,
f,
MATCH(
2,
1/m
)-f+1
)
)
)
)Excel solution 18 for Trim Underscores at Ends, proposed by roberto mensa:
=LET(
a,
SEQUENCE(
,
10
),
r,
REPT(
"_",
a
),
s,
MAX(
COUNTIF(
A1,
r&"*"
)*a
),
e,
MAX(
COUNTIF(
A1,
"*"&r
)*a
),
MID(
A1,
s+1,
LEN(
A1
)-s-e
)
)Excel solution 19 for Trim Underscores at Ends, proposed by Tushar Mehta:
=LAMBDA(
rng,
LET(
_name,
"Remove leading and trailing underscores",
removeLeading,
LAMBDA(
f,
s,
IF(
LEFT(
s,
1
)="_",
f(
f,
RIGHT(
s,
LEN(
s
)-1
)
),
s
)
),
removeTrailing,
LAMBDA(
f,
s,
IF(
RIGHT(
s,
1
)="_",
f(
f,
LEFT(
s,
LEN(
s
)-1
)
),
s
)
),
MAP(
rng,
LAMBDA(
s,
removeTrailing(
removeTrailing,
removeLeading(
removeLeading,
s
)
)
)
)
)
)(A2:A8)Excel solution 20 for Trim Underscores at Ends, proposed by Victor Yemitan:
=LET(
data,
A2:A8,
u,
"_",
REDUCE(
data,
SEQUENCE(
MAX(
LEN(
data
)
)
),
LAMBDA(
x,
y,
IF(
LEFT(
x
)=u,
REPLACE(
x,
1,
1,
""
),
IF(
RIGHT(
x
)=u,
REPLACE(
x,
LEN(
x
),
1,
""
),
x
)
)
)
)
)Solving the challenge of Trim Underscores at Ends with SQL
SQL solution 1 for Trim Underscores at Ends, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA
AS
(
SELECT 'Ve__nus_' AS STRING UNION ALL
SELECT '_#Earth_' AS STRING UNION ALL
SELECT 'Jupiter' AS STRING UNION ALL
SELECT '___$$M_erc__ury%____' AS STRING UNION ALL
SELECT '2_3_Pluto_____' AS STRING UNION ALL
SELECT '____N_e_p_t_u_n__e__' AS STRING UNION ALL
SELECT '09_Mars!__' AS STRING
)
SELECT
D.STRING
,TRIM('_' FROM D.STRING) AS RESULT
FROM DATA D
;
&&