We help IT Professionals succeed at work.
Get Started

Data Cleansing - Text Manipulation - VBA Code

Mehawitchi
Mehawitchi asked
on
740 Views
Last Modified: 2012-08-13
Hello experts,

I'm looking for VBA code that would remove the text between brackets, including the brackets, so the line:
COTY: CK/ DAVIDOFF: SEQUEL SATURDAYS:  THE BOURNE IDENTITY (2002, PG-13, 119 min,*3/3)
should become:
COTY: CK/ DAVIDOFF: SEQUEL SATURDAYS:  THE BOURNE IDENTITY

The above operation should be applied on every line in a movie list that is supplied in an Excel sheet. Please see below sample of that list:

COTY: CK/ DAVIDOFF: SEQUEL SATURDAYS:  THE BOURNE IDENTITY (2002, PG-13, 119 min,*3/3)
RENAULT: SEQUEL SATURDAYS:  THE BOURNE SUPREMACY (2004, PG-13, 108 min,*1/3)
PETROMIN: THE LAST KING OF SCOTLAND (2006, R, 123 min, +2)
KFC: CONSPIRACY (2008, R, 90 min, 2/4)
WOLF CREEK (2005, R, 99 min, 4/4)
THE SILENCE OF THE LAMBS (1991, R,118 min, *3/4)
JOY RIDE (2001, 97 min,*3/4)
FORD: MONDAY NIGHT PREMIERE: THE HITCHER (2007, R, 84 min, 1/4)
THE TEXAS CHAINSAW MASSACRE (2003, R, 98 min, 6/6)
POWER HORSE: DOOM (2005, R, 100 min, +2)
SNICKERS: HOME OF MOVIES: THE BOOK OF ELI (2010, R, 118 min, 1/6)
RAMBO IV (2008, R, 92 min,*1/99)
KIA: DARK BLUE (2002, R, 118 min,*4/4)
STC: 16 BLOCKS (2006, 105 min, *1/4)
ADIDAS: NAKED WEAPON (2002, R, 92 min, *1/99)
WRIGLEYS: TORNADO! (1996, PG, 89 min,1/3)
STC:  DOMINO (2005, R, 127 min,5/99)
GRAND CHEROKEE: CHILLER: HOSTEL (2005, R, 94 min, 1/3)
CREEP (2004, R, 85 min,*1/99)
PETROMIN: THE MANCHURIAN CANDIDATE (2004, R, 129 min,*1/4)
SLITHER (2006, R, 95 min, *1/6)

I'm also attaching a sample Excel sheet for your reference.

The VBA code should read line by line and remove the entire part between brackets, which is the release year of the movie, Rating, Duration, etc..)

The good news is that you will only have one left "(" and one right ")" brackets, so your code should be straightforward in trying to locate the position of those brackets in the entire string and simply do a replace or any other text manipulating function to remove it.

Also, the list is in one column, so I can select the colum, and then run the code with :


For each cell in Selection
.....
Replace ....
....
next cell

End sub


Appreciate your help
 Movie-List.xlsx
Comment
Watch Question
VBA Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE