Random Number Formula needed

Question

I need your help. I want to generate a series of Random numbers(min 1000 numbers). format is: XG123456789J01 ( XG 123456789 J01) XG and J are permanent in numbers. I am very confused about how to generate a random number using a combination of text and digits. Please guide me. Thanks

Answer 1

You can use the following formula to Generate Random UNIQUE Numbers:

---

• Formula used in cell A1

=TEXT(INT(RAND()*10^11),"X\G\ 000000000 J00")

---

Or, If you are presently using MS365 then using RANDARRAY()

---

• Formula used in cell A1

=TEXT(INT(RANDARRAY(10)*10^11),"X\G\ 000000000 J00")

---

Notes: The use of backslash before & after the G is an escape character. Because the X & G on its own serves a different purpose, we are escaping it meaning hence asking Excel to literally form text with that character. Also the above formulas will return Unique Random Numbers. Since you need min 1000 numbers as per OP then just change the 1st param of RANDARRAY() from 10 to 10^3 or 1000 in the formula.

---

If you want to know more on Escaping Character aka Backslash you can read this post from this forum answered by chuff

How can I escape special characters in cell formatting in Excel

---

Also I would like to add a Caveat which has been already mentioned in comments by Scott Craner Sir about the VOLATILE nature of the functions used i.e. RAND() & RANDARRAY()

Just so you know Formula are live and any Random formula is volatile, as in it will change whenever Excel recalculates, which is on every change on any cell in any open workbook. If you want to create them and leave them then you will need vba or copy/paste special just the values to remove the formula.

---

Answer 2

To generate single number you can use a formula:

=REPLACE(TEXT(RAND(),"X\G,000000000J00"),3,1,)

Then you can copy it as many times as required.
If you have new version of Excel with RANDARRAY function you can generate many numbers at once e.g. 100

=REPLACE(TEXT(RANDARRAY(100),"X\G,000000000J00"),3,1,)

(In English version of Excel the character \ probably is redundant).
Of course results are volatile and you probably need to covert them to permanent values.