Removing parentheses

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

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

---
HTH
Jason
Atlanta, GA



>-----Original Message-----
>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
>
>
>.
>

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Brett" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

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

"Jason Morin" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> ---
> HTH
> Jason
> Atlanta, GA
>
>
>
>>-----Original Message-----
>>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
>>
>>
>>.
>>

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 wrote:
>
> 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
>
> "Jason Morin" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > ---
> > HTH
> > Jason
> > Atlanta, GA
> >
> >
> >
> >>-----Original Message-----
> >>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
> >>
> >>
> >>.
> >>

--

Dave Peterson

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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
>>
>> "Jason Morin" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>> > ---
>> > HTH
>> > Jason
>> > Atlanta, GA
>> >
>> >
>> >
>> >>-----Original Message-----
>> >>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
>> >>
>> >>
>> >>.
>> >>
>
> --
>
> Dave Peterson

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


"Jason Morin" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> ---
> HTH
> Jason
> Atlanta, GA
>
>
>
>>-----Original Message-----
>>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
>>
>>
>>.
>>