Removing parentheses

[Brett]

Not sure if this is the correct group. Please advise me if not.

What will a VBA macro look like that removes all parentheses from the first
column of an Excel worksheet and then sets the format for that column to
"general"?

Thanks,
Brett

 

[Jason Morin]

You could record a macro under Tools > Macros to do this,
or try this:

Sub DeleteParen()
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Set ws = ActiveSheet
With ws
Set rng1 = .Range("A:A")
Set rng2 = .UsedRange
End With
Set rng3 = Application.Intersect(rng1, rng2)
With rng3
.Replace What:="(", Replacement:="", LookAt:=xlPart
.Replace What:=")", Replacement:="", LookAt:=xlPart
End With
Range("A:A").NumberFormat = "General"
End Sub

 

[Max]

This might also work ..

Put in B1:

=TEXT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")),"General")

Copy B1 down

Then do a copy on col B,
paste special as values to overwrite col A,
and delete col B

 

[Brett]

I like the code. Thanks.

I opened the VB Editor and pasted into spreadsheet1. I can save it as an
xls file. However, when I reopen Excel and do alt+F8, the Macro won't be
listed there. How do I have it listed it in the Macro section everytime I
open Excel on this machine?

Is there a way to create a keyboard shortcut to it?

Also, could you do a little line by line describing of what the is doing?

Thanks,
Brett

 

[Dave Peterson]

If you save that workbook to your xlstart folder, then each time you start
excel, this workbook will be opened (and the macro will be available).

Lots of people use a workbook with the name of personal.xls for this kind of
thing.

And they'll even make it so that personal.xls workbook is hidden--so it doesn't
get in the way when you're swapping between workbooks.

 

[Brett]

I saved the Personal.xls file to the xlsstart folder. It does load on
startup now and I can see the macro in Book1. However, when I run the macro
from Book1, nothing happens. I can see the macro is referencing the
Personal workbook in its name. Does the macro only work in the Personal
workbook rather than across work books?

Thanks,
Brett

 

[Brett]

I saved the macro as personal.xls. It works fine as long as I'm in that
workbook. If I open a new workbook and call the macro, it doesn't work
right. For example, I enter this in column A

(301) 256-8965
3015489666

Then I format the column as special | Phone number. You'll notice the first
row (with literals) doesn't reformat. It will reformat if you are in the
personal workbook. Why is that?

Thanks,
Brett