# a little odd: Need to convert TODAY() date to two digits 0000 and use 2 letters + 6 random number formula

#### nesbie

##### New Member
Hello,

I'm breaking my head here, I understand a little =Tex which changes my TODAY() cell in another cell to 0406. Now I would like to add initials at the front like CS0406 followed by 6 random numbers. CS0406123456- It's killing me, can someone help?

It doesn't have to pull the date from the =TODAY() cell, if there's a formula that can do all of the above, that would rock.

Can someone please guide me as I get stuck using TEXT/RANDOM etc.

Been searching for hours.

Chris.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Rick Rothstein

##### MrExcel MVP
Hello,

I'm breaking my head here, I understand a little =Tex which changes my TODAY() cell in another cell to 0406. Now I would like to add initials at the front like CS0406 followed by 6 random numbers. CS0406123456- It's killing me, can someone help?

It doesn't have to pull the date from the =TODAY() cell, if there's a formula that can do all of the above, that would rock.
I think this does what you want...

=TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)

#### nesbie

##### New Member
OMG! YESSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS!!!!!!!!!!!!!!!!!!!!!!

Let me buy you a drink one day that you're in LA!!! Seriously! I hate how I spent hours trying to find out a solution, not knowing if I should go with TEXT, RANDOM etc and you figured it out quickly!!

I'm truly thankful sir!

Was worried when I clicked save if the same numbers would open and everytime new numbers appear (hence randbetween) thank you!

#### Joe4

I think this does what you want...

=TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)
The only issue is that that the value will keep changing with every calculation of the sheet (because RANDBETWEEN will keep re-evaluating).
If you need to "lock it" at a certain value doesn't change, you will need to convert the formula to a hard-coded value, either manually or by VBA.
I guess it depends on how they are using it, if that is going to be a problem or not.

Last edited:

#### nesbie

##### New Member
The only issue is that that the value will keep changing with every calculation of the sheet (because RANDBETWEEN will keep re-evaluating).
If you need to "lock it" at a certain value doesn't change, you will need to convert the formula to a hard-coded value, either manually or by VBA.
I guess it depends on how they are using it, if that is going to be a problem or not.

It's working fine but any changes I make on a different cell, it generates a new number! UGH. I thought it was just when I closed and re-open it gave a new number but it's at anytime while it's open... how can I change this?!

#### nesbie

##### New Member
You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)

#### Joe4

You're 100% correct - how can I go about locking it manually (assuming this will be the fastest route vs VBA)
Copy -> Paste Special Values would do it.
If VBA, I would recommend doing the whole calculation in VBA. The question is, when you run this VBA code, where do you want it to place the value?
What is the logic of that?

#### nesbie

##### New Member
I just have a simple form, I'm have a field for user/address/number (this is where the code sits) and it repeats the same thing on 6 rows. When I open the log or make any changes, the CS0406000000 changes, which sits on the tab 'number' - not sure if this makes sense or answers your question. If I had a VBA code to put the 'numbers' row, that would solve my problem.

#### Joe4

Here is some VBA code that calculates the value you want. I have it returning to a MsgBox, but you can have it put the value wherever you need it:
Code:
``````Sub RandNum()

Dim Low As Double
Dim High As Double
Dim R As Double
Dim ID As String

Low = 100000
High = 999999
R = Int((High - Low + 1) * Rnd() + Low)

ID = Format(Date, "\C\Smmdd") & R

MsgBox ID

End Sub``````
Got a little help from here: http://www.cpearson.com/excel/randomnumbers.aspx

#### Rick Rothstein

##### MrExcel MVP
Here is some VBA code that calculates the value you want. I have it returning to a MsgBox, but you can have it put the value wherever you need it:
Code:
``````Sub RandNum()

Dim Low As Double
Dim High As Double
Dim R As Double
Dim ID As String

Low = 100000
High = 999999
R = Int((High - Low + 1) * Rnd() + Low)

ID = Format(Date, "\C\Smmdd") & R

MsgBox ID

End Sub``````
Got a little help from here: http://www.cpearson.com/excel/randomnumbers.aspx
Alternately, this one-liner will do the same thing as the code you posted...
Code:
``````[table="width: 500"]
[tr]
[td]Sub RandCode()
MsgBox [TEXT(TODAY(),"\C\Smmdd")&RANDBETWEEN(100000,999999)]
End Sub[/td]
[/tr]
[/table]``````

Last edited: