Editing large text cells

[Manolocs]

Hello, I am wondering if there is a way to check a list of columns for a missing close parenthesis, I mean there is an open parenthesis, without a close ou vice versa.
Thanks in advance.

 

[W8253]

If your column is in A:A starting A1,

=IFERROR((IF(FIND("(",A1)>0,"")),"( Missing")&IFERROR((IF(FIND(")",A1)>0,""))," ) Missing")

 

[Manolocs]

If your column is in A:A starting A1,

=IFERROR((IF(FIND("(",A1)>0,"")),"( Missing")&IFERROR((IF(FIND(")",A1)>0,""))," ) Missing")

Hi W8253, If the cell is empty or with any text without the "()" the return is "(missing) Missing" It would be better for me if only look for open or close parenthesis.

 

[W8253]

Hi W8253, If the cell is empty or with any text without the "()" the return is "(missing) Missing" It would be better for me if only look for open or close parenthesis.

=CONCATENATE(IF(A1="","",IFERROR(IF(FIND("(",A1)>0,""),"Open missing ")),IF(A1="","",IFERROR(IF(FIND(")",A1)>0,""),"Close missing")))

Does this work?

 

[Manolocs]

if there is no parenthesis at all still return the "Open missing Close missing"
the idea is to show only if it is missing the close if there is an open parenthesis and vice versa.

 

[István Hirsch]

Give this a try:

=LOOKUP(SIGN(LEN(SUBSTITUTE(A1,"(",""))-LEN(SUBSTITUTE(A1,")",""))),{-1,0,1},{"Missing )","","Missing ("})

 

[Manolocs]

Give this a try:

=LOOKUP(SIGN(LEN(SUBSTITUTE(A1,"(",""))-LEN(SUBSTITUTE(A1,")",""))),{-1,0,1},{"Missing )","","Missing ("})

That is great save my day.... work as expected.....
Thank you very much for your time István Hirsch

 

[István Hirsch]

You are welcome.

 

[Rick Rothstein]

Give this a try:

=LOOKUP(SIGN(LEN(SUBSTITUTE(A1,"(",""))-LEN(SUBSTITUTE(A1,")",""))),{-1,0,1},{"Missing )","","Missing ("})

There is one problem your formula, it will not identify a backward matching pair (it will return the empty text string "" for that condition). What I mean by backward matching is, in its simplest form (you can ugly it up by adding several properly matching pairs)...

abc)defg(hijk

I am not sure a formula solution exists, but I do have a UDF (user defined function) that will work...

Function CheckParen(ByVal S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!()]" Then Mid(S, X) = " "
  Next
  S = Replace(S, " ", "")
  Do While Len(S) > 1 And S Like "*()*"
    S = Replace(S, "()", "")
  Loop
  If S = ")(" Then
    CheckParen = "Backward"
  ElseIf Len(S) Then
    CheckParen = "Missing " & Choose(InStr("()", S), ")", "(")
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CheckParen just like it was a built-in Excel function. For example,

=CheckParen(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[István Hirsch]

Thanks Rick, you are right, a formula should handle backward matching pairs too.
By the way, when I try the Function in post #9 with „(((ksfsf)gdg” I get „Missing” as a result.