+ Reply to Thread
Results 1 to 5 of 5

Macro to remove hyphen from number

  1. #1
    Registered User
    Join Date
    08-10-2007
    Posts
    2

    Macro to remove hyphen from number

    I need to remove a hyphen from a number in a cell, move down one cell and repeat-15000 times. The format will always be XX-XXXX. I need to end up with XXXXXX as the result. I used to be able to do this in Lotus 123, but that's dating me. Thanks in advance.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Macro to remove hyphen from number

    You may not need a macro to remove the hyphens.

    However, your values fall into 1 of 2 categories:

    1) They are numbers FORMATTED to have a hyphen in them.
    There won't be any hyphens to find/replace, so you only need to apply a different number format:
    Select the cells
    <format><cells><number tab>
    Category: Custom
    Type: 000000
    Click [OK]

    OR

    2) They are text values with a hyphen in them.
    Select the cells
    <edit><replace>
    Find what: -
    Replace with: <---leave this blank
    Click [Replace All]

    With the hyphens removed, the values will be converted to numbers. Any leading zeros would then disappear. If you want them back, apply the custom format from above.

    .....OR....

    You could set the number format to Text first, then remove the hyphens.
    Select the cells
    <format><cells><number tab>
    Category: Text........Click [OK]

    Now replace the hyphens......the values will be Text, so leading zeros will display.

    Does that help?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    08-10-2007
    Posts
    2

    Thanks!

    #2 was the one. Thanks for the advice.

  4. #4
    Registered User
    Join Date
    09-28-2012
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Macro to remove hyphen from number

    This should be SOOOOO simple - but it's just not working.
    I have:
    00848
    008-678
    008-900-BB
    All in one column formatted as "TEXT"
    I want to get rid of the "-" and keep the zeros...
    Edit-Replace-"-" with ""(null)...and...removes the hyphens and leading zeros...

    Any Ideas ???

  5. #5
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to remove hyphen from number

    dbonnell1972,

    Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1