A text number with exactly 6 digits in the final outcome

1. 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

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

Good evening mjwillyone
Originally Posted by mjwillyone
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

3. 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.``

4. 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.``

5. 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. 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. 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. Re: A text number with exactly 6 digits in the final outcome

Maybe

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

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

Dear VBS Noob,

Thanks so much! It works great!

Mike

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

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