Random Letter and Number Generator

[buzz71023]

Good afternoon all,
I am looking for a way to generate a random 3 letter and 4 digit number in excel. Through a little research, most methods are saying to use CHAR and RANDBETWEEN.
this is the formula I have currently.

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)&RANDBETWEEN(10,99)

This does give a desired random number but anytime a cell changes, the number updates. I need the number to be constant once generated. Is there anyway to do this?

For what it's worth, this is a small delivery appointment spreadsheet and this number that is generated cannot change because it is given to the other party.

 

[Joe4]

You can use VBA to turn it into a hard-coded value, once set, i.e.
If your formula was in cell A1, this VBA code can change it to a hard-coded value:

Range("A1").Value = Range("A1").Value

It might be important for us to undertand how exactly you are using this and when you are deriving the values.
If you give us more details on the end-to-end process, we can probably come up with a solution for you.

 

[buzz71023]

You can use VBA to turn it into a hard-coded value, once set, i.e.
If your formula was in cell A1, this VBA code can change it to a hard-coded value:

Range("A1").Value = Range("A1").Value

It might be important for us to undertand how exactly you are using this and when you are deriving the values.
If you give us more details on the end-to-end process, we can probably come up with a solution for you.

I thought about that but I was trying to get away with doing it exclusively with a formula. This will most likely be going to Microsoft Teams' version of Excel once completed.

I added a little more detail on how it will be used to the original post as you were replying. Bad timing.

 

[Joe4]

I thought about that but I was trying to get away with doing it exclusively with a formula.

It cannot be done exclusively with a formula, because it is dynamic and will be constantly recalculated (kind of like when you use the NOW function to return current date and time - it is constantly changing!). So you will need to then change it to hard-coded value, either manually or via VBA code.

Here is some code that will run and populate whatever the active cell is with a hard-coded random value:

Sub MyRandomGenerator()
    ActiveCell.FormulaR1C1 = _
        "=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)&RANDBETWEEN(10,99)"
    ActiveCell.Value = ActiveCell.Value
End Sub

 

[Joe4]

Alternatively, if you wanted to do a whole range of cells at once, you can select that range then run this version:

Sub MyRandomGenerator2()
    Dim cell As Range
    For Each cell In Selection
        cell.FormulaR1C1 = _
            "=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(10,99)&RANDBETWEEN(10,99)"
        cell.Value = cell.Value
    Next cell
End Sub