Announcement

Collapse
No announcement yet.

Remove Parenthesis (Brackets) From Numbers

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Deleting () From Fax Number

    If they're all of exactly the same format:

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

    Comment


    • #3
      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, 05:30. Reason: figured it out

      Comment


      • #4
        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?

        Comment


        • #5
          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?

          thanks for your help!

          Comment


          • #6
            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.

            Comment


            • #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.
              AAE
              ----------------------------------------------------

              Forum Rules | Message to Cross Posters | How to use Tags

              Comment


              • #8
                Re: Deleting () From Fax Number

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

                Comment


                • #9
                  Re: Deleting () From Fax Number

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

                  Comment


                  • #10
                    Re: Deleting () From Fax Number

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

                    Comment


                    • #11
                      Re: Deleting () From Fax Number

                      Yet another way;


                      Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
                       
                       =SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","") 

                      Comment

                      Working...
                      X