There are 3 types of Parentheses (brackets) – ( ), { }, [ ]. In A2:A10, for every opening bracket, there has to be a closing bracket and vice versa. You need to find what brackets are missing if there is an opening/closing bracket. Order doesn’t matter Example – For { ( } ]=> )[ are missing
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 289
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Identify Missing Brackets with Power Query
Power Query solution 1 for Identify Missing Brackets, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
{{"(", ")"}, {"[", "]"}, {"{", "}"}},
(d) =>
let
n = Text.Length(Text.Select([String], d{0})) - Text.Length(Text.Select([String], d{1}))
in
if n <> 0 then
Text.Repeat((if n < 0 then d{0} else d{1}) & " ", Number.Abs(n))
else
null
),
""
)
)
in
AnsPower Query solution 2 for Identify Missing Brackets, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
P = {"{", "[", "(", ")", "]", "}"},
N = (c) => List.Reverse(P){List.PositionOf(P, c)},
S = Table.TransformRows(
Source,
each Text.Combine(
List.Transform(
List.Accumulate(
Text.Split([String], " "),
{},
(s, c) => if List.Contains(s, N(c)) then List.Difference(s, {N(c)}) else s & {c}
),
N
),
" "
)
)
in
SPower Query solution 3 for Identify Missing Brackets, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.Select(Text.ToList([String]), each _ <> " "),
b = List.ReplaceMatchingItems(
a,
{{"(", ")"}, {")", "("}, {"{", "}"}, {"}", "{"}, {"[", "]"}, {"]", "["}}
),
c = Text.Combine(List.Difference(b, a), " ")
][c]
)
in
resPower Query solution 4 for Identify Missing Brackets, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Pairs = List.Split(Text.ToList("{}[]()}{][)("), 2),
Ans = Table.AddColumn(
Source,
"Expected Answer",
each
let
StrLst = Text.ToList([String]),
Complement = List.ReplaceMatchingItems(StrLst, Pairs),
Diff = List.Difference(Complement, StrLst)
in
Text.Combine(Diff)
)
in
AnsSolving the challenge of Identify Missing Brackets with Excel
Excel solution 1 for Identify Missing Brackets, proposed by Bo Rydobon 🇹🇭:
=TRIM(REDUCE("",SEQUENCE(20,,,2),LAMBDA(a,n,LET(p,"([{}])",
b,MID(A2:A10,n,1),IF(ISERR(FIND(b,a)),a&" "&MID(p,7-FIND(b,p),1),SUBSTITUTE(a,b,,1))))))Excel solution 2 for Identify Missing Brackets, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,
LAMBDA(a,
LET(p,
{"(",
")";"{",
"}";"[",
"]"},
m,
MMULT(
LEN(
a
)-LEN(
SUBSTITUTE(
a,
p,
)
),
{1;-1}
),
TRIM(CONCAT(IF(m,
REPT(INDEX(p,
{1;2;3},
1+(m>0))&" ",
ABS(
m
)),
""))))))Excel solution 3 for Identify Missing Brackets, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
f,
LAMBDA(
l,
r,
LET(
s,
SUM(
{-1,
1}*LEN(
SUBSTITUTE(
a,
HSTACK(
l,
r
),
""
)
)
),
REPT(
CHOOSE(
2+SIGN(
s
),
l,
,
r
)&" ",
ABS(
s
)
)
)
),
TRIM(
f(
"(",
")"
)&f(
"[",
"]"
)&f(
"{",
"}"
)
)
)
)
)Excel solution 4 for Identify Missing Brackets, proposed by John V.:
=MAP(A2:A10,LAMBDA(x,LET(s,{"(",")";"[","]";"{","}"},c,LEN(x)-LEN(SUBSTITUTE(x,s,)),TRIM(CONCAT(REPT(s&" ",BYROW(c,LAMBDA(r,MAX(r)))-c))))))Excel solution 5 for Identify Missing Brackets, proposed by محمد حلمي:
=MAP(A2:A10,LAMBDA(r,LET(
e,LAMBDA(a,b,LET(
i,LEN(SUBSTITUTE(r,a,))-LEN(SUBSTITUTE(r,b,)),
REPT(a&" ",(i>0)*i)&REPT(b&" ",(i<0)*-i))),
e("(",")")&e("{","}")&e("[","]"))))Excel solution 6 for Identify Missing Brackets, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(y,LET(a,TEXTSPLIT(y,," "),b,HSTACK({"(";"[";"{"},{")";"]";"}"}),c,MMULT(MAP(b,LAMBDA(x,SUM(--(a=x)))),{1;-1}),TRIM(TEXTJOIN(" ",,IF(c<0,REPT(TAKE(b,,1)&" ",ABS(c)),REPT(TAKE(b,,-1)&" ",c)))))))Excel solution 7 for Identify Missing Brackets, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
z,
LET(
F,
LAMBDA(
n,
LEN(
z
)-LEN(
CONCAT(
TEXTSPLIT(
z,
n
)
)
)
),
G,
LAMBDA(
n,
m,
IF(
F(
n
)>F(
m
),
REPT(
m&" ",
F(
n
)-F(
m
)
),
IF(
F(
n
)Excel solution 8 for Identify Missing Brackets, proposed by Hussein SATOUR:
=MAP(A2:A10,
LAMBDA(y,
LET(
a,
{"(";"{";"["},
b,
{")";"}";"]"},
c,
TEXTSPLIT(
y,
,
" "
),
d,
MAP(a,
LAMBDA(x,
SUM((c = x)*1))),
e,
MAP(b,
LAMBDA(x,
SUM((c = x)*1))),
f,
d-e,
TRIM(
CONCAT(
IFS(
f<0,
REPT(
a&" ",
ABS(
f
)
),
f>0,
REPT(
b&" ",
ABS(
f
)
),
1,
""
)
)
))))Excel solution 9 for Identify Missing Brackets, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A10,
LAMBDA(i,
LET(_f,
LAMBDA(
x,
y,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
y,
""
)
)
),
a,
_f(
i,
{"(",
"{",
"["}
) -_f(
i,
{")",
"}",
"]"}
),
CONCAT(REPT(MID({"()",
"{}",
"[]"},
1+(SIGNOa)>0),
1)&" ",
ABS(
a
))))))Excel solution 10 for Identify Missing Brackets, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(str,
LET(
_par, WRAPROWS({"("; ")"; "{"; "}"; "["; "]"}, 2),
_tbl, MAP(_par, LAMBDA(a, IFERROR(ROWS(UNIQUE(TOCOL(SEARCH(a, str, SEQUENCE(20)), 3))), 0))),
_diff, TAKE(_tbl, , 1) - TAKE(_tbl, , -1),
TEXTJOIN(
" ",
,
MAP(
_diff,
TAKE(_par, , 1),
TAKE(_par, , -1),
LAMBDA(a, b, c, IFS(a > 0, REPT(c, a), a < 0, REPT(b, ABS(a)), 1, ""))
)
)
)
)
)Excel solution 11 for Identify Missing Brackets, proposed by Pieter de Bruijn:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
b,
{"[",
"]";"(",
")";"{",
"}"},
c,
LEN(
a
)-LEN(
SUBSTITUTE(
a,
b,
""
)
),
d,
TAKE(
c,
,
1
)-DROP(
c,
,
1
),
e,
TRIM(
CONCAT(
REPT(
INDEX(
b,
ROW(
1:3
),
IF(
d<0,
1,
2
)
)&" ",
ABS(
d
)
)
)
),
e
)
)
)Excel solution 12 for Identify Missing Brackets, proposed by Giorgi Goderdzishvili:
=MAP(
A3:A11,
LAMBDA(
x,
LET(
arr_1,
{"(",
"{",
"["},
arr_2,
{")",
"}",
"]"},
st,
x,
sb,
LEN(
SUBSTITUTE(
st,
arr_1,
""
)
)-LEN(
SUBSTITUTE(
st,
arr_2,
""
)
),
fin,
CONCAT(
IF(
sb>=0,
REPT(
arr_1,
ABS(
sb
)
),
REPT(
arr_2,
ABS(
sb
)
)
)
),
IFERROR(
TEXTJOIN(
" ",
TRUE,
MID(
fin,
SEQUENCE(
,
LEN(
fin
)
),
1
)
),
""
)
)
)
)Excel solution 13 for Identify Missing Brackets, proposed by Abdelrahman Omer, MBA, PMP:
=MAP(A2:A10,
LAMBDA(a,
LET(b,
MID(
TRIM(
a
),
SEQUENCE(
LEN(
a
)
),
1
),
d,
BYCOL(1*(UNICODE(
b
)={40,
41,
91,
93,
123,
125}),
LAMBDA(
x,
SUM(
x
)
)),
g,
BYROW(
WRAPROWS(
d,
2
),
LAMBDA(
x,
MAX(
x
)
)
)-WRAPROWS(
d,
2
),
h,
CONCAT(
REPT(
CHAR(
{40,
41;91,
93;123,
125}
),
g
)
),
IFERROR(
TRIM(
CONCAT(
MID(
h,
SEQUENCE(
LEN(
h
)
),
1
)&CHAR(
32
)
)
),
""
))))Excel solution 14 for Identify Missing Brackets, proposed by Anup Kumar:
=BYROW(A2:A10,LAMBDA(x, LET(
arr,TEXTSPLIT(x,," "),
a, SUM(--(arr="(")),
b, SUM(--(arr =")")),
c, SUM(--(arr ="{")),
d, SUM(--(arr ="}")),
e, SUM(--(arr ="[")),
f, SUM(--(arr ="]")),
ab, IFS(a>b,REPT(") ",a-b),b>a, REPT("( ",b-a),TRUE,""),
cd, IFS(c>d,REPT("} ",c-d),d>c, REPT("{ ",d-c),TRUE,""),
ef, IFS(e>f,REPT("] ",e-f),f>e, REPT("[ ",f-e),TRUE,""),
CONCAT(ab,cd,ef)
)))Excel solution 15 for Identify Missing Brackets, proposed by Kriddakorn Pongthanisorn:
=BYROW(
A2:A10,
LAMBDA(
r,
LET(
_t,
SUBSTITUTE(
r,
" ",
""
),
_d1,
"(){}[]",
_d2,
")(}{][",
_da1,
MID(
_d1,
SEQUENCE(
LEN(
_d1
)
),
1
),
_da2,
MID(
_d2,
SEQUENCE(
LEN(
_d2
)
),
1
),
_ts,
MID(
_t,
SEQUENCE(
LEN(
_t
)
),
1
),
_tn,
TEXTJOIN(
" ",
1,
XLOOKUP(
_ts,
_da1,
_da2
)
),
_r,
TRIM(
REDUCE(
_tn,
_ts,
LAMBDA(
& _i,
_a,
SUBSTITUTE(
_i,
_a,
"",
1
)
)
)
),
_r
)
)
)Excel solution 16 for Identify Missing Brackets, proposed by Kriddakorn Pongthanisorn:
=BYROW(
A2:A10,
LAMBDA(
_r,
LET(
_t,
SUBSTITUTE(
_r,
" ",
""
),
_st,
ARRAYFORMULA(
MID(
_t,
SEQUENCE(
LEN(
_t
)
),
1
)
),
_code,
TEXTJOIN(
" ",
1,
ARRAYFORMULA(
UNICHAR(
SWITCH(
UNICODE(
_st
),
41,
40,
40,
41,
123,
125,
125,
123,
91,
93,
93,
91
)
)
)
),
_r,
TRIM(
REDUCE(
_code,
_st,
LAMBDA(
ini,
ar,
SUBSTITUTE(
ini,
ar,
"",
1
)
)
)
),
_r
)
)
)Excel solution 17 for Identify Missing Brackets, proposed by Emad Falahnezhad:
=LET(
A,
LEN(
A2
)-LEN(
SUBSTITUTE(
A2,
"(",
""
)
),
B,
LEN(
A2
)-LEN(
SUBSTITUTE(
A2,
")",
""
)
),
IF(
A>B,
REPT(
") ",
A-B
),
IF(
AB,
REPT(
"] ",
A-B
),
IF(
AB,
REPT(
"} ",
A-B
),
IF(
A Solving the challenge of Identify Missing Brackets with Python in Excel
Python in Excel solution 1 for Identify Missing Brackets, proposed by Bo Rydobon 🇹🇭:
import functools as f
[' '.join(f.reduce(lambda a, v: a.replace(v,'',1) if v in a else a+(q:="([{}])")[5-q.index(v)] , x.split(),'')) for x in xl("A2:A10")[0]]Python in Excel solution 2 for Identify Missing Brackets, proposed by Bo Rydobon 🇹🇭:
[''.join((c[n>0]+' ')*abs(n) for c in ['()','[]','{}'] if (n:=a.count(c[0])- a.count(c[1]))) for a in xl("A2:A10")[0]]Python in Excel solution 3 for Identify Missing Brackets, proposed by John V.:
Hi everyone!
One (Python) option could be:
b = "([{)]}"
r = []
for i in xl("A2:A10")[0]:
n = [i.count(x) for x in b]
c = ""
for j in range(3):
m = max(n[j], n[j+3])
c += (b[j] + ' ') * (m - n[j]) + (b[j+3] + ' ') * (m - n[j+3])
r.append(c.strip())
r
Blessings!
Solving the challenge of Identify Missing Brackets with R
R solution 1 for Identify Missing Brackets, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Missing Parenthesis.xlsx")
balance_parentheses <- function(input_str) {
sorted_chars <- strsplit(input_str, NULL)[[1]] %>% sort()
char_counts <- table(sorted_chars)
get_diff <- function(open_char, close_char) {
open_count <- ifelse(is.na(char_counts[open_char]), 0, as.numeric(char_counts[open_char]))
close_count <- ifelse(is.na(char_counts[close_char]), 0, as.numeric(char_counts[close_char]))
diff_count <- open_count - close_count
if (diff_count > 0) {
return(paste0(rep(close_char, diff_count), collapse = ""))
} else {
return(paste0(rep(open_char, abs(diff_count)), collapse = ""))
}
}
complement_str <- paste0(
get_diff('(', ')'),
get_diff('[', ']'),
get_diff('{', '}')
)
return(complement_str)
}
result = input %>%
mutate(my_answer = map_chr(.$String, balance_parentheses))
&&