Serial Number Generator

**mel2131** · 07-13-2018, 04:37 AM

Hi
Not sure if im in the right place (excel), but i am looking to set up a serial number generator for my company.
Preferably alpha numeric 12-14 digits.
I dont mind if they follow in sequence, but randomized would be totally awesome....but then would i need to go down a different route like programming or something? I have no experience with this so its not an option for me.

Can anyone advise me where to start?

**PaulM100** · 07-13-2018, 04:41 AM

This will provide random numbers between 12 - 14 digits
=RANDBETWEEN(100000000000,99999999999999)
or type a number in A1. in A2: =A1+1 and drag it down to be in order.

**AliGW** · 07-13-2018, 04:43 AM

Here's one way:

Excel 2016 (Windows) 32 bit

	A
1	Header
2	SN100000001
3	SN100000002
4	SN100000003
5	SN100000004
6	SN100000005
7	SN100000006
8	SN100000007
9	SN100000008
10	SN100000009
11	SN100000010
12	SN100000011

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	A
2	="SN"&100000000+COUNTA(A$1:A1)

Sheet: Sheet1

You could set each row to be blank until other data appears on the row.

**mel2131** · 07-13-2018, 04:47 AM

WoW thanks Paul that was quick. Its it possible to:
Add letters? (i guess i could do this in a separate cell anyway)
Say how many digits it should be?
Make sure there are no duplicates?

**AliGW** · 07-13-2018, 04:49 AM

See my offering above.

RANDBETWEEN does not generate UNIQUE numbers, so be aware of that - you could, in theory, end up with duplicate IDs.

**PaulM100** · 07-13-2018, 04:59 AM

To create a unique list of random number you will need helper columns. Honestly, I would go with Ali's formula as it keeps it simple.

**Glenn Kennedy** · 07-13-2018, 05:02 AM

Why do you want them in random order? That seems a bit bizarre, IMHO.

**mel2131** · 07-13-2018, 05:07 AM

Thanks Ali, i had missed your post, looks like exactly what i need in a very simple way. Thanks again.

**AliGW** · 07-13-2018, 05:07 AM

Thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Glenn Kennedy** · 07-13-2018, 05:09 AM

See the attached sheet, which uses this array formula:

="ABCD"&TEXT(LARGE(ROW($1:$1000)*NOT(COUNTIF($A$1:A1, ROW($1:$1000))), RANDBETWEEN(1,1001-ROW(A1))),"000")

to generate random, non-repeating numbers. bear in mind, however, that RANDBETWEEN is volatile and the serial numbers will change every time ANYTHING changes on your sheet.

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

My view: Stick with Ali's approach.