Data Cleansing & Text Formulas with Excel 2010

Hello Experts,

Below is a very interesting VBA procedure that would remove any text between brackets:

     Sub supprparen()
        For Each cel In Range("A:A")
           Do While InStr(cel, "(") > 0
              cel.Value = Replace(cel, Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1), "")
           Loop
        Next cel
     End Sub

For example, the following text:
OMI (ANNEM) (DRAMA EDIT V.)

Will become:
OMI

I need to revise the above procedure in order to adress two small issues:

1) Some text will have one bracket only "(" without the end bracket ")". Following is an example:
OMI (ANNEM) (DRAMA EDIT V


 This causes the procedure to stop with an error. Accordingly, we need to handle the error by either leaving the text as is or removing it "(DRAMA EDIT V"

2) The other issue is that I need to keep the season information with the TV program name. For example:
Desperate Houswives (7)  ---> This means Season 7, and I have to keep it, so I want to build some logic into the procedure that if the content between the brackets is numeric, then keep it.

Appreciate your help.
Hani
MehawitchiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
For the first issue

     Sub supprparen()
        For Each cel In Range("A:A")
           Do While InStr(cel, "(") > 0 and InStr(cel, ")") > 0
              cel.Value = Replace(cel, Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1), "")
           Loop
        Next cel
     End Sub
0
Saqib Husain, SyedEngineerCommented:
I do not have access to excel at the moment but try replacing

cel.Value = Replace(cel, Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1), "")


with

if val(Mid(cel, InStr(cel, "(")+1, InStr(cel, ")") - InStr(cel, "(") - 1))>0 then
   cel.Value = Replace(cel, Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1), "")
end if
0
Rory ArchibaldCommented:
Try this:
Sub supprparen()
    Dim RegExp           As Object
    Dim strPattern       As String
    Set RegExp = CreateObject("vbscript.regexp")
    strPattern = "(\([^\d]+\))"
    With RegExp
        .Global = True
        .Pattern = strPattern
        For Each cel In Range("A:A")
            If Len(cel.Value) > 0 Then cel.Value = .Replace(cel.Value, "")
        Next cel
    End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

MehawitchiAuthor Commented:
Thanks ssaqibh/rorya - Both your solutions for first problem worked.

As for 2nd problem (keeping the brackets that include number), the solution suggested by ssaqibh got me into an endless loop without solving the issue.

I understand you (ssaqibh) don't have access to Excel for trial now, but once you have a chance, you probably need to tweak it a little bit

Thank you
0
Rory ArchibaldCommented:
Mine should work for numbers inside brackets.
0
Saqib Husain, SyedEngineerCommented:
Ok Try this

     Sub supprparen()
        For Each cel In Range("A:A")
           ss = ""
           Do While InStr(cel, "(") > 0 And InStr(cel, ")") > 0
                If Val(Mid(cel, InStr(cel, "(") + 1, InStr(cel, ")") - InStr(cel, "(") - 1)) > 0 Then
                    ss = ss & Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1)
                End If
                   cel.Value = Replace(cel, Mid(cel, InStr(cel, "("), InStr(cel, ")") - InStr(cel, "(") + 1), "")
           Loop
           If ss <> "" Then cel.Value = cel.Value & ss
        Next cel
     End Sub
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.