Data Cleansing & Text Formulas with Excel 2010
Posted on 2012-03-12
Below is a very interesting VBA procedure that would remove any text between brackets:
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), "")
For example, the following text:
OMI (ANNEM) (DRAMA EDIT V.)
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.