random number between 000 and 999

acura123

Board Regular
Joined
Jul 13, 2003
Messages
87
How do I generate a random number between 000 and 999

I am using Int((999 * Rnd) + 0) but It produces 2 digit numbers and 3 digit numbers.

It leaves off the first 0


Thanks
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
use the RANDBETWEEN function, written like this + RANDBETWEEN(0,999) and drag down all the cells you want randon numbers in.

if you do it on a lot of cells however be aware you may get duplicate

HTH
:)
 
Upvote 0
The problem I have is that I want all the numbers to be 3 digits in length

so for example

0 would be 000
01 would be 001

In other words formated for 3 digits in length.

I am also using this in a text box in access
 
Upvote 0
ok, well i don't know a formula that will generate three digits but i do know a work around. in cell A1 do the same formule, but make the number between 0 and 9 then drag that across B1 and C1. Then in another cell where you want the three digits to display, for instance D1 type =CONCATENATE(A1,B1,C1)

That will get you your 3 digit result. Again if you want it in lot of cells just drag to suit. shows up leading zeros as you wished

:)
 
Upvote 0
once you've done that you can press F9 and you'll keep getting new numbers. i've tried it several times now and have had 008 as a number so it'll even generate 2 leading zeros, which means it will also display 3 if you happen to generate that
 
Upvote 0
Do you want the number to be 001, or to display as 001?

For the first option you would need to convert it to text -- RIGHT("000" & Int((999 * Rnd) + 0),3)

For the second, go to the textbox or to the table field and set the format as 000. Note, however, that 001 will actually be 1 wehn you run a search for it.

Denis
 
Upvote 0
I'm sure this will work in access, except it's screaming at me that it's missing a reference library. Which one do I need to work with len, right, mid?
 
Upvote 0
Press Alt+F11 to go to the code window. Tools > References will most likely show a reference as MISSING. Note which it is, uncheck the reference, scroll down the list and select the latest available version of that library. Click OK and then close the code window and run the query / code again.

Denis
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top