Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Add Leading Zero to a cell based on length of cell contents.

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Add Leading Zero to a cell based on length of cell contents.

    Hi All,

    I am trying to make excel automatically add a leading zero to values which are 5 digits long;

    i.e. number input is 15185, then excel automatically changes it to 015185.

    If I put a Customer Number Format of 0##### it works, however, a user could put any length of number into these cells, and if the number is less than 5 digits I don't want a leading zero.

    Is there any way of writing a small macro to sort this out.

    The numbers would be input into range B16:223.

    Many thanks,


    Andy

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,298
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add Leading Zero to a cell based on length of cell contents.

    Formula? Maybe
    =IF(LEN(A1)<5,A1,TEXT(A1,"000000"))

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Add Leading Zero to a cell based on length of cell contents.

    Try the number format:

    [>9999]000000;General
    Microsoft MVP - Excel

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Add Leading Zero to a cell based on length of cell contents.

    Thank you both so much!

    Andrew I ended up using yours and it works perfectly!

    Fantastic, speedy response.

    Much appreciated.


    Andy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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