Creating unique reference IDs in Excel

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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

14 thoughts on “Creating unique reference IDs in Excel

  1. Adam

    Or use CONCATENATE(“IMPORTA-“,RIGHT(“0000000″&ROW(B13)-1,4)) changing the last 4 to the length of the number that you want

  2. Andy

    Ahh yeah. I might try that out too – will it differentiate between the length of different numbers? I can’t visualise that without Excel in front of me.

  3. anonny

    Is there a way that we can say add the month and date on it? I.e. Importa-O1105
    So instead of just the row it would show say the sign up month and date?

  4. Kim

    What if you have a tracking list in excel that you want to create a unique id for; but you also want to be able to sort it. How can this be done without the sort changing the number?

  5. Kevin

    In Excel, is there a way of generating a unique ID starting at say 1 and incrementing by 1 in each row without having to copy down the formula?

  6. sz

    If I have a unique ID with letters and numbers and I want it to sort numerical, how do I do that?
    FOr example, I have ET1, ET2, ET8, ET10, ET12. When I click sort, instead of listing in 1,2,3,4, consecutively, excel sorts like the following: ET1, ET10, ET11, ET12, ET13, ET2, ET3. Thank you.

Comments are closed.