Remove Parenthesis (Brackets) From Numbers

[twin9000]

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

[StephenR]

If they're all of exactly the same format:

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

[twin9000]

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?

[StephenR]

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?

[twin9000]

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?

thanks for your help!

[StephenR]

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.

[AAE]

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.

[twin9000]

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

[mikerickson]

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

[mdmackillop]

Sub FixFax() 
    Columns(1).Replace "(", "" 
    Columns(1).Replace ") ", "" 
End Sub