Could one of the resident macro experts kindly help me to do this
please. There is text in column D of a worksheet. I want to remove
anything that is in brackets, including the brackets.

So this:

A B C D
-- -- --------------- -------------------------------------------
1 P Andrews Sisters Boogie Woogie (1941 version)
2 Q Artie Shaw (1941) Concerto For Clarinet (Parts 1 & 2)
3 R Artie Shaw Dancing In The Dark
4 S Artie Shaw Frenesi

becomes this:
1 P Andrews Sisters Boogie Woogie
2 Q Artie Shaw Concerto For Clarinet
3 R Artie Shaw Dancing In The Dark
4 S Artie Shaw Frenesi

Thanks.

--
Terry, West Sussex, UK

You can get very close to what you want by selecting column D
edit|replace
what: (*) 'open paren, asterisk, close paren
with: (leaveblank) 'don't type anything--nothing!
replace all

The use a helper column of formulas like:

=trim(d1)
(and drag down)

Then convert to values and delete the original column D.

In code, it would look something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

myRng.Replace what:="(*)", replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

For Each myCell In myRng.Cells
myCell.Value = Application.Trim(myCell.Value)
Next myCell

End Sub


Terry Pinnell wrote:

Could one of the resident macro experts kindly help me to do this
please. There is text in column D of a worksheet. I want to remove
anything that is in brackets, including the brackets.

So this:

A B C D
-- -- --------------- -------------------------------------------
1 P Andrews Sisters Boogie Woogie (1941 version)
2 Q Artie Shaw (1941) Concerto For Clarinet (Parts 1 & 2)
3 R Artie Shaw Dancing In The Dark
4 S Artie Shaw Frenesi

becomes this:
1 P Andrews Sisters Boogie Woogie
2 Q Artie Shaw Concerto For Clarinet
3 R Artie Shaw Dancing In The Dark
4 S Artie Shaw Frenesi

Thanks.

--
Terry, West Sussex, UK

--

Dave Peterson

Dave Peterson wrote:

You can get very close to what you want by selecting column D
edit|replace
what: (*) 'open paren, asterisk, close paren
with: (leaveblank) 'don't type anything--nothing!
replace all

The use a helper column of formulas like:

=trim(d1)
(and drag down)

Then convert to values and delete the original column D.

In code, it would look something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

myRng.Replace what:="(*)", replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

For Each myCell In myRng.Cells
myCell.Value = Application.Trim(myCell.Value)
Next myCell

End Sub

Thanks very much, kind of you to take the trouble. I'll give it a try.
Meanwhile I resorted to RegEx! I eventually managed to do it by
pasting that column into my text editor and using a Find of
(.+)
and replacing it with nothing.

(Note that TextPad's RegEx implementation is unusual. The equivalent
in 'standard PERL' would be
\(.+\)

Could you remind me in really no-brainer terms what steps I take to
get that code pasted correctly into Excel 2000 please? As you may have
seen from my earlier post, I've forgotten what little I once learned
<g.

BTW, that earlier example from Don did not have an 'Option Explicit'
line at the top. I see mine 'inherited' one somehow. I vaguely recall
I set it up to do that automatically years ago, and suspect this may
be implicated in my difficulties?

--
Terry, West Sussex, UK

The easy part first.

"Option Explicit" tells excel that you're going to declare each variable you use
with a Dim statement.

Adding this to the top of your module can cause "problems" when all the
variables are not declared--but not having this can cause more and more
difficult to find problems.

For instance, with this kind of code:

Option Explicit
Sub testme()
Dim Rowl As Long
Rowl = 0
For Rowl = 1 To 10
MsgBox Row1
Next Rowl
End Sub

You won't even be able to run it. (one of those row-ell's is row-one--difficult
to catch with the naked eye.)

But with option explicit, the VBA editor will even yell at you what line is
incorrect.

If you drop the Option explict, then the VBA editor won't care if you didn't
declare all your variables (since you didn't tell it to care).

The code would run--but it would show an empty message box--Row1 (with one, not
ell) is implicitly a variant (dim xxx as Variant)--and it's never been set to
anything--so you see an empty string.

Adding "Option Explicit" may seem like it makes more work for you, but it really
stops you from spending hours looking for that #^!#@%ing typo.

And you can tell the VBE to add this line to all new modules:
Inside the VBE:
Tools|options|Editor tab
check "require variable declaration"

===========
To add the procedure to your workbook....

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and save this workbook (so you don't have to do this
portion again). (And change the name to something more meaningful than Testme.)

Then test it out via:

tools|macro|macros...
select the macro and click run.

Terry Pinnell wrote:

Dave Peterson wrote:

You can get very close to what you want by selecting column D
edit|replace
what: (*) 'open paren, asterisk, close paren
with: (leaveblank) 'don't type anything--nothing!
replace all

The use a helper column of formulas like:

=trim(d1)
(and drag down)

Then convert to values and delete the original column D.

In code, it would look something like:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("D1", .Cells(.Rows.Count, "D").End(xlUp))
End With

myRng.Replace what:="(*)", replacement:="", _
lookat:=xlPart, searchorder:=xlByRows, _
MatchCase:=False

For Each myCell In myRng.Cells
myCell.Value = Application.Trim(myCell.Value)
Next myCell

End Sub

Thanks very much, kind of you to take the trouble. I'll give it a try.
Meanwhile I resorted to RegEx! I eventually managed to do it by
pasting that column into my text editor and using a Find of
(.+)
and replacing it with nothing.

(Note that TextPad's RegEx implementation is unusual. The equivalent
in 'standard PERL' would be
\(.+\)

Could you remind me in really no-brainer terms what steps I take to
get that code pasted correctly into Excel 2000 please? As you may have
seen from my earlier post, I've forgotten what little I once learned
<g.

BTW, that earlier example from Don did not have an 'Option Explicit'
line at the top. I see mine 'inherited' one somehow. I vaguely recall
I set it up to do that automatically years ago, and suspect this may
be implicated in my difficulties?

--
Terry, West Sussex, UK

--

Dave Peterson

Translation...
From:
but not having this can cause more and more difficult to find problems.
to:
but not having this can make it more difficult to find problems.

<<snipped

Dave Peterson wrote:

The easy part first.

"Option Explicit" tells excel that you're going to declare each variable you use
with a Dim statement.

Adding this to the top of your module can cause "problems" when all the
variables are not declared--but not having this can cause more and more
difficult to find problems.

For instance, with this kind of code:

Option Explicit
Sub testme()
Dim Rowl As Long
Rowl = 0
For Rowl = 1 To 10
MsgBox Row1
Next Rowl
End Sub

You won't even be able to run it. (one of those row-ell's is row-one--difficult
to catch with the naked eye.)

But with option explicit, the VBA editor will even yell at you what line is
incorrect.

If you drop the Option explict, then the VBA editor won't care if you didn't
declare all your variables (since you didn't tell it to care).

The code would run--but it would show an empty message box--Row1 (with one, not
ell) is implicitly a variant (dim xxx as Variant)--and it's never been set to
anything--so you see an empty string.

Adding "Option Explicit" may seem like it makes more work for you, but it really
stops you from spending hours looking for that #^!#@%ing typo.

And you can tell the VBE to add this line to all new modules:
Inside the VBE:
Tools|options|Editor tab
check "require variable declaration"

===========
To add the procedure to your workbook....

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and save this workbook (so you don't have to do this
portion again). (And change the name to something more meaningful than Testme.)

Then test it out via:

tools|macro|macros...
select the macro and click run.

Many thanks, appreciate your taking the trouble to spell that out -
very helpful.

--
Terry, West Sussex, UK