I often need to create unique IDs in an Excel spreadsheet for importing into our database system. Doing this by hand is time-consuming and error-prone, so here is a method I’ve found which saves a lot of time and effort.

Let’s say I want to create an ID in the format “IMPORTA-1234”, where 1234 is a unique number from 1 to the total number of records in the spreadsheet. However, I would also like them neatly formatted (purist that I am), so that record 5 is shown as IMPORTA-0005 not IMPORTA-5, record 124 is shown as IMPORTA-0124 not IMPORTA-124, and so on.

1. Create a blank column in your spreadsheet, if one doesn’t already exist, for the ID. Call it something like ImportID or UniqueID.

2. Enter the following formula into the first data row (probably row 2 of the spreadsheet):
=CONCATENATE("IMPORTA-",LEFT("0000",4-LEN(ROW(A2)-1)),ROW(A2)-1)
(where A2 is the reference of the cell the formula is in)

3. Now copy and paste this into each row – being a relative formula ensures that each ROW references the correct cell.

What does this do? Let’s look at each part of the formula in turn:

CONCATENATE – This creates one string comprising the three distinct elements needed to form a unique ID.

• Element 1 is the static “IMPORTA-” string.
• Element 2 is the appropriate number of ‘0’ characters to pad out shorter numbers.
• Element 3 is (row number minus one) which gives a unique number based on the record’s position in the spreadsheet (and accounting for a header row)

To ensure that the correct number of zeroes are used as padding, we use the LEFT function to grab a portion of the string “0000”. We use the ROW(cell reference)-1 function to get the unique number, and we use 4-LEN(ROW(cell reference)-1) to work out how many characters long that number is.

Let’s walk through a couple of examples to show how this works:

For row number 25, which is record 24:

• First part of string is “IMPORTA-“
• We now use LEFT(“0000”,4-LEN(ROW(A25)-1)).
  The length of ROW(A25)-1 is 2 (24 is two characters long)
  The (4-2) left most characters of “0000” are “00”
• Final part of the string is the (row number – 1) = 24

Output ID = IMPORTA-0024

For row number 164, which is record 163:

• First part of string is “IMPORTA-“
• We now use LEFT(“0000”,4-LEN(ROW(A164)-1)).
  The length of ROW(A164)-1 is 3 (163 is three characters long)
  The (4-3) left most character of “0000” is “0”
• Final part of the string is the (row number – 1) = 163

Output ID = IMPORTA-0163

and so on.

Once the spreadsheet is complete (no more records are to be inserted or deleted) it may be a good idea to ‘lock down’ the IDs by highlighing all the ID cells, copying them into the clipboard, and then doing a ‘Paste Special’ specifying to paste the actual values. This removes the formulae and makes the IDs static, but still unique of course.

To adapt for longer or shorter numbers, change the string “0000” and the number that the row length is subtracted from (in this case 4). For records from 001 to 999, use "000" and 3-LEN(ROW(reference); for records from 00001 to 99999, use "00000" and 5-LEN(ROW(reference), and so on.