asked on

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

Saqib Husain

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

SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mehawitchi

ASKER

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

Rory Archibald

Mine should work for numbers inside brackets.

Saqib Husain

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

Data Cleansing &amp; Text Formulas with Excel 2010

Data Cleansing & Text Formulas with Excel 2010