+ Reply to Thread
Results 1 to 7 of 7

Normalize Numbers

  1. #1
    Registered User
    Join Date
    03-01-2009
    Location
    Woodmere, New York
    MS-Off Ver
    Excel 2007
    Posts
    31

    Normalize Numbers

    Can some one please assist. I have a column of alpha numeric numbers with leading zeros which i need to normalize. (remove leading zeros).

    They have been copied from another spread sheet where they are formatted as General.

    Can a macro do this?
    Example Need to Convert to
    Column F
    HOUSEAWB
    02269MTA 2269MTA
    1524GLC 1524GLC
    0078TCL 78TCL
    0100AZO 100AZO
    0102AZ 102AZO
    0111TMB 11TBM


    THANK YOU
    Last edited by tallguy6354; 04-25-2009 at 09:15 PM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Normalize Numbers

    a couple of functions in another column can do it
    either if format is
    00123abc or 0123def ect i.e there are always numbers before letters then
    =MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99
    will do it
    however if you may have 000abc123
    =IF(MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))<=LEN(A1),MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99),SUBSTITUTE(A1,"0",""))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    03-01-2009
    Location
    Woodmere, New York
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Normalize Numbers

    I would probably use the latter function. However i am not programer and need to be able to incorperate your solution into an existing macro. How would I select collumn F to row 2000 to check for these zeros.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Normalize Numbers

    I would just turn on the macro recorder and let it record you entering that formula into an adjacent cell, then copy and EDIT > PASTESPECIAL > VALUE back over the original cell. Then turn off the recorder.

    Now you have a VBA version of how to enter that .FormulaR1C1 = into your original code.

    As you move that code into your macro, apply the formula to the ENTIRE range. I haven't test the actual formula much, but once you've done as I've suggested above, you get something like this:
    Please Login or Register  to view this content.
    As you can see, I'm entering the formulas into column M. You can change that to any empty column out to the right.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Normalize Numbers

    TRY
    Please Login or Register  to view this content.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Normalize Numbers

    Quote Originally Posted by martindwilson View Post
    TRY
    Please Login or Register  to view this content.
    Short and Sweet! Very nice...

  7. #7
    Registered User
    Join Date
    03-01-2009
    Location
    Woodmere, New York
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Normalize Numbers

    Thanks to all.

    It works perfectly>

+ 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