+ Reply to Thread
Results 1 to 16 of 16

Applying PADNUM (padd numbers formula)

  1. #1
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Applying PADNUM (padd numbers formula)

    Hi

    I have been helped with a brilliant UDF formula which will pad numbers e.g. 1 becomes 001

    Currently I have a hierarchy which is impossible to sort correctly e.g. in its current view 1.11 appears before 1.6 which I want to avoid.

    I have been playing around with the data and I think that if I am able to apply the padnum formula to column A then somehow then this would work (I hope). E.g. In the example above this would become 001.011 and 001.006 which would mean sort would work.

    In the attached example in column E I have the desired result – is there any way to achieve this with formula? Please note there is no consistency on the number of dots, it ranges from 1 to 10.

    Can anyone help please?
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Applying PADNUM (padd numbers formula)

    Hi Paul,
    This would require two formulas.
    1. Enter formula in G2 and copy across to cell N2 and then down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    then enter 2nd formula in F2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Please see attached file.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Applying PADNUM (padd numbers formula)

    Something like:

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Applying PADNUM (padd numbers formula)

    Hi Alkey - that does the trick. Do you mind explaining how the G:N formula works please? If a level has say 20 levels can I simply drag it across the correct number of cells?

    @Trevor - how do I apply that? Is it a UDF or Macro?

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Applying PADNUM (padd numbers formula)

    Quote Originally Posted by pauldaddyadams View Post
    Hi Alkey - that does the trick. Do you mind explaining how the G:N formula works please? If a level has say 20 levels can I simply drag it across the correct number of cells?
    Yes, simply drag formula to the right. Just make sure you add cells to the CONCATENATE part too.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Applying PADNUM (padd numbers formula)

    By your examples, the formula you want in C2 and copied down is

    =Padnum(A2 & "." & B2,3)

    A
    B
    C
    D
    1
    Level Act ID Unique Number
    2
    1.11 1 001.011.001 C2: =Padnum(A2 & "." & B2,3)
    3
    1.11 2 001.011.002
    4
    1.11 3 001.011.003
    5
    1.12 1 001.012.001
    6
    1.12 2 001.012.002
    7
    1.12 3 001.012.003
    8
    1.6 1 001.006.001
    9
    1.6 2 001.006.002
    10
    1.11.1 1 001.011.001 .001
    11
    1.11.1 2 001.011.001 .002
    12
    1.11.1 3 001.011.001 .003
    13
    1.11.1 4 001.011.001 .004
    14
    1.11.2 2 001.011.002 .002
    15
    1.11.2 3 001.011.002 .003
    16
    1.11.2 4 001.011.002 .004
    17
    1.11.7.1.2.1 1 001.011.007.001.002.001 .001
    18
    1.11.7.4.4 6 001.011.007.004.004 .006
    19
    1.11.7.5 1 001.011.007.005 .001
    20
    1.12.1.2.3 1 001.012.001.002.003 .001
    21
    1.12.1.4.2.7.11 8 001.012.001.004.002.007.011 .008
    22
    1.12.1.4.2.7.11 9 001.012.001.004.002.007.011 .009
    23
    1.6.9.2.3.2 1 001.006.009.002.003.002 .001
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Applying PADNUM (padd numbers formula)

    Also, you probably want to remove the trailing spaces that are in some entries in column A. Then it will sort like this:

    A
    B
    C
    1
    Level Act ID Unique Number
    2
    1.6
    1
    001.006.001
    3
    1.6
    2
    001.006.002
    4
    1.6.9.2.3.2
    1
    001.006.009.002.003.002.001
    5
    1.11
    1
    001.011.001
    6
    1.11.1
    1
    001.011.001.001
    7
    1.11.1
    2
    001.011.001.002
    8
    1.11.1
    3
    001.011.001.003
    9
    1.11.1
    4
    001.011.001.004
    10
    1.11
    2
    001.011.002
    11
    1.11.2
    2
    001.011.002.002
    12
    1.11.2
    3
    001.011.002.003
    13
    1.11.2
    4
    001.011.002.004
    14
    1.11
    3
    001.011.003
    15
    1.11.7.1.2.1
    1
    001.011.007.001.002.001.001
    16
    1.11.7.4.4
    6
    001.011.007.004.004.006
    17
    1.11.7.5
    1
    001.011.007.005.001
    18
    1.12
    1
    001.012.001
    19
    1.12.1.2.3
    1
    001.012.001.002.003.001
    20
    1.12.1.4.2.7.11
    8
    001.012.001.004.002.007.011.008
    21
    1.12.1.4.2.7.11
    9
    001.012.001.004.002.007.011.009
    22
    1.12
    2
    001.012.002
    23
    1.12
    3
    001.012.003
    Last edited by shg; 09-16-2016 at 01:17 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Applying PADNUM (padd numbers formula)

    It's another UDF, similar to PadNum and utilising PadNum.

    That said, it looks as though you can just use PadNum on its own to do what you want (as shg has demonstrated)

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Applying PADNUM (padd numbers formula)

    Hi,

    Here's another UDF
    Enter
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  10. #10
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Applying PADNUM (padd numbers formula)

    Hi Shg - your solution is great, I didn't know that was possible. How do I remove the trailing spaces with formula, I tried =TRIM(Padnum(A10 & "." & B10,3)) but this didn't do it

    @Alkey - I assume using the columns formula is how it works out the 1st, 2nd, 3rd occurrence of the . - is that correct?

    @TMS - I still haven't been able to test the code, what would the formula be?

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Applying PADNUM (padd numbers formula)

    Quote Originally Posted by pauldaddyadams View Post
    @Alkey - I assume using the columns formula is how it works out the 1st, 2nd, 3rd occurrence of the . - is that correct?
    Yes, that is correct.

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Applying PADNUM (padd numbers formula)

    Here's a shorter UDF
    It's also a little less rigorous, Richard

  13. #13
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Applying PADNUM (padd numbers formula)

    For example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  14. #14
    Valued Forum Contributor
    Join Date
    04-21-2005
    Location
    Southern England
    MS-Off Ver
    Excel for Office 365
    Posts
    1,689

    Re: Applying PADNUM (padd numbers formula)

    Hi

    Just wanted to say thanks for all the help!!

    I have provided a file as a reference for anyone else that needs this - I couldn't find much online about it when researching it.
    Attached Files Attached Files

  15. #15
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Applying PADNUM (padd numbers formula)

    Quote Originally Posted by shg View Post
    It's also a little less rigorous, Richard
    Yes I should perhaps have qualified it by saying that it assumes the first group will always be 001, and that each group is never more than 3 digits, which was the case for the test data. But apart from that I think it's working OK. Famous last words...

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Applying PADNUM (padd numbers formula)

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. applying conditional formatting to numbers in brackets
    By thejarv in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2014, 05:19 PM
  2. want to call list name if condition padd
    By Rughveda in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-10-2012, 08:04 AM
  3. problem with converting numbers to TEXT format and applying comma style
    By Vogelmann in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-23-2012, 10:43 AM
  4. Padd field with null characters
    By djacobsen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-22-2012, 09:46 AM
  5. Macro: Applying formula to multiple cells in excel (formula editing)
    By city in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 06:41 AM
  6. [SOLVED] applying formula
    By Pat N. in forum Excel General
    Replies: 1
    Last Post: 04-25-2006, 03:50 PM
  7. [SOLVED] Rounding off numbers in Excel 2003:applying decimal points
    By Joe Tahoe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM
  8. sequence numbers:applying filter.
    By Ashley in forum Excel General
    Replies: 1
    Last Post: 07-22-2005, 11:05 AM

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