# Thread: Random Number (MSO 2003/2007)

1. ## Random Number (MSO 2003/2007)

I have a list of numbers, which are all 10 digits. What I am trying to do is generate a 3 digit random number from those 10 number combinations.
I also want it to show this way: xxx W xxx The formula I used was =RANDBETWEEN(1,B3) and I formated the cell to (000 "W" 000). The formula works, and the format works for the numbers behind the "W" but in front of the "W" I get 6 digits. I tried reducing it to a single 0 before the "W" but I still get 6 digits.
Ideas?

2. ## Re: Random Number (MSO 2003/2007)

=LEFT(RANDBETWEEN(1,\$B\$3),3) & " W " & LEFT(RANDBETWEEN(1,\$B\$3),3)

3. ## Re: Random Number (MSO 2003/2007)

Thanks Jerry that seemed to work. I copied and pasted it into the appropriate cell, and removed the "\$" so that the formula would generate to the adjacent cell and not the first cell (see attachment) Now however when I change any of the numbers in Column "B", or add a number, column "D" re-generates a random number for all of them. In looking in the help in Excel, it says that by putting the "=" sign there it should not do that.

4. ## Re: Random Number (MSO 2003/2007)

The RAND and RANDBETWEEN functions will be recalculated, i.e. generate a new random number, each time the worksheet is recalculated.
You can edit the formula (press F2), then press F9 to replace the formula with its result.

5. ## Re: Random Number (MSO 2003/2007)

<img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Good point. Then my quick and dirty way to get around it would be a cut and paste special and select value <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

I tried taking away auto calculate in the options menu to no avail <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

6. ## Re: Random Number (MSO 2003/2007)

Perfect Guys! thank you.

#### Posting Permissions

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