+ Reply to Thread
Results 1 to 9 of 9

A text number with exactly 6 digits in the final outcome

  1. #1
    Registered User
    Join Date
    11-25-2003
    Location
    Decatur, Alabama
    Posts
    94

    A text number with exactly 6 digits in the final outcome

    Dear Friends,

    I am wanting to create a VB script that will take a number (in general format) of two or three digits, convert it to text, then make sure that there are enough zeros preceeding the number to make exactly 6 digits. Examples of the initial number (before the macro is run) and the final number (after it is run):

    initial number: 34 (general format)
    final number: 000034 (in text format)


    initial number: 345 (general format)
    final number: 000345 (in text format)

    Can anyone help?

    Thank you so much,
    Mike
    Learn to Serve Others. Kindness is far better than the alternative.

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile Re: A text number with exactly 6 digits in the final outcome

    Good evening mjwillyone
    Quote Originally Posted by mjwillyone View Post
    I am wanting to create a VB script that will take a number (in general format) of two or three digits, convert it to text, then make sure that there are enough zeros preceeding the number to make exactly 6 digits.
    Why does it have to be a macro?

    You can do this perfectly well with a simple formula, so why complicate matters?

    =IF(LEN(TEXT(A1,"General"))=2,"0000"&A1,"000"&A1)

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Forum Contributor GuruWannaB's Avatar
    Join Date
    01-24-2008
    Location
    An hour due East of Cowtown Ohio
    MS-Off Ver
    2010
    Posts
    421

    Re: A text number with exactly 6 digits in the final outcome

    Wish I could take credit for this...but just saw this on another post I was on

    Please Login or Register  to view this content.
    Last edited by GuruWannaB; 03-04-2009 at 06:21 PM. Reason: tags
    I help because of the Pavlovian dog that resides in the inner me...so if you are happy with the results, please add to my reputation. It helps keep me motivated!



    Please mark your threads as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: A text number with exactly 6 digits in the final outcome

    Hello mjwillyone,

    Here is the requested VBA script. This changes the cell's format to text so the leading zeroes will be displayed.
    Please Login or Register  to view this content.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  5. #5
    Registered User
    Join Date
    11-25-2003
    Location
    Decatur, Alabama
    Posts
    94

    Re: A text number with exactly 6 digits in the final outcome

    I am building it into a larger script for office automation purposes.

    Mike

  6. #6
    Registered User
    Join Date
    11-25-2003
    Location
    Decatur, Alabama
    Posts
    94

    Re: A text number with exactly 6 digits in the final outcome

    Dear Leith,

    Thank you so much for the help. I just noticed that I failed to mention something in my original post. I am wanting the script to make this change to all cells in column B. How would I do that?

    Thanks again,
    Mike

  7. #7
    Registered User
    Join Date
    11-25-2003
    Location
    Decatur, Alabama
    Posts
    94

    Re: A text number with exactly 6 digits in the final outcome

    Can anyone help with my last question? How can I get the VB script to process through an entire column?

    Thanks,
    Mike

  8. #8
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988

    Re: A text number with exactly 6 digits in the final outcome

    Maybe

    Please Login or Register  to view this content.
    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  9. #9
    Registered User
    Join Date
    11-25-2003
    Location
    Decatur, Alabama
    Posts
    94

    Re: A text number with exactly 6 digits in the final outcome

    Dear VBS Noob,

    Thanks so much! It works great!

    Mike

+ 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