Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Remove Parenthesis (Brackets) From Numbers

1. I agreed to these rules
Join Date
3rd February 2009
Posts
4

## Remove Parenthesis (Brackets) From Numbers

I have a spread sheet with 2000 fax numbers in the format:
(555) 123-1234

I am going through and reformatting them to read:

555123-1234 or 5551231234

Is there cell format code or some way to do this quickly rather than going through each cell and deleting the spaces and ()?

thank you

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
21st February 2006
Location
London, UK
Posts
3,867

## Re: Deleting () From Fax Number

If they're all of exactly the same format:

=MID(A1,2,3)&MID(A1,7,8)

Excel Video Tutorials / Excel Dashboards Reports

3. I agreed to these rules
Join Date
3rd February 2009
Posts
4

## Re: Deleting () From Fax Number

So I paste then in a cell next to the number and it works.
But how do I quickly do it to all 2000 entries without changing the forumla for each cell?
Last edited by twin9000; February 3rd, 2009 at 05:30. Reason: figured it out

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
21st February 2006
Location
London, UK
Posts
3,867

## Re: Deleting () From Fax Number

If your numbers are in column A, starting in A1, then enter this formula in A2 and copy down as far down as necessary. Are you asking how to enter a formula?

Excel Video Tutorials / Excel Dashboards Reports

5. I agreed to these rules
Join Date
3rd February 2009
Posts
4

## Re: Deleting () From Fax Number

Ok, so I paste the formula in cell B2 and it shows the number correctly for cell A2, groovy. But I have a new different number is cell A3, A4, A5... A2000. How can I get the corresponding B cells to appear correctly?

Also do I need to leave the original row A of numbers with the orginal (123) 123-1234 format? Or is there a way to clear those out and JUST have the new format 123123-1234?

Excel Video Tutorials / Excel Dashboards Reports

6. Super Moderator
Join Date
21st February 2006
Location
London, UK
Posts
3,867

## Re: Deleting () From Fax Number

Once you've entered the first formula in B2, click on that cell and hover over the bottom-right corner until the cursor turns into a cross. Then double-click and it should copy all the way down.

If you select all the formulae cells in column B, Edit > Copy, then select A2, Edit > PasteSpecial > Values and then you can delete column B.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Deleting () From Fax Number

twin9000,

Just in case you haven't yet found this link, and based on the assumed experience in your profile, you may be interested in this free Excel training from Ozgrid: http://www.ozgrid.com/Excel/free-tra...asic-index.htm.

8. I agreed to these rules
Join Date
3rd February 2009
Posts
4

## Re: Deleting () From Fax Number

sweet dude! Thanks! You saved me hours of brainless work!

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Deleting () From Fax Number

From the Edit menu you could use Find & Replace (thrice) to replace ( ) and <space> with nothing.

Excel Video Tutorials / Excel Dashboards Reports

10. I agreed to these rules
Join Date
10th December 2004
Posts
3

## Re: Deleting () From Fax Number

VB:
```Sub FixFax()
Columns(1).Replace "(", ""
Columns(1).Replace ") ", ""
End Sub

```

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno