Generating a Unique ID Number

Written by Allen Wyatt (last updated May 4, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


2

Amy wants to create a unique ID for a membership database she is currently storing in Excel. She wonders if there is a formula that can do this easily.

The short answer is "yes, you can do this." But it is not a simple matter of somehow saying to Excel, "give me a unique identifier." The program has no way of knowing what that means; it is something that you must define.

For instance, the simplest unique identifier you can come up with is a series of sequential numbers. These are, by definition, unique. They may not, however, fit your needs. You may want, instead, ID numbers that are not sequential. Or you may want them to contain digits and letters. Or you may want them to consist of just letters. Or you may want them to always be a certain length. Or you may want the ID number to be based on something related to the member. Or... well, you get the idea: You must define what you want a "unique ID number" to look like. Once you know this, you can utilize Excel to come up with a number that fits the bill.

For example, let's say that you want the ID number to be just digits, but not sequential. You also want it to be six digits in length. You could easily accomplish that by putting the following into cell A1:

=TEXT(RANDBETWEEN(1,999999),"000000")

Now, copy the formula down as many cells as you desire. The chances of you generating non-unique values is pretty slim, but the ID numbers will regenerate (because of the RANDBETWEEN function) each time the worksheet recalculates. So, at some point, you'll want to make the ID numbers static by selecting the range of cells, pressing Ctrl+C, and then using Paste Special to paste values in place of the original formulas.

To absolutely ensure you have no duplicates in the list, you can use the Remove Duplicates tool from the Data tab of the ribbon. Or, if you are using Excel 2021 or later or Excel in Microsoft 365, you can ensure you have no duplicates by using the following formula in cell B1:

=UNIQUE(A1:A250)

Just make sure that you use the range of cells in column A that contain your ID numbers. The result, in column B, is however many IDs you need, verified to be unique and fitting your defined needs for the IDs.

If you want to modify what your ID numbers include, then all you need to do is go back to the beginning of this described process and modify the formula you use in cell A1. For instance, you may want something super-simple that consists of just the letters "MN" (for "member number") followed by a sequential five-digit number. All you need to do is type this into cell A1:

MN00001

Now, select that cell and drag the fill handle downward. You now have all your membership numbers.

Or, lets say you want the IDs to consist of three uppercase letters, followed by a dash, followed by four digits, then you could use the following in cell A1:

=CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & CHAR(RANDBETWEEN(65, 90)) & "-" & TEXT(RANDBETWEEN(1,9999),"0000")

Copy it downward and use the same process described earlier to make the values static and to remove any potential duplicates.

If you want to get super-secure with the ID number you come up with, there are ways to do so. (Granted, this is like shooting a mosquito with a cannon—a bit of overkill—but it may be necessary for some users.) Here is a macro that allows you to create a hashed string for whatever "seed" information you pass to it:

Public Function HashString(Text As String, T As Integer) As String
    Dim Encode As Object
    Dim Crypto As Object
    Dim Provider(3) As String
    Dim Hash() As Byte
    Dim n As Integer
    Dim Response As String

    Const Security = "System.Security.Cryptography"
    Provider(1) = ".MD5CryptoServiceProvider"    '16 bytes, 32 chars
    Provider(2) = ".SHA1CryptoServiceProvider"   '20 bytes, 40 chars
    Provider(3) = ".SHA256Managed"               '32 bytes, 64 chars

    If (T > 0) And (T < 4) Then
        Set Encode = CreateObject("System.Text.UTF8Encoding")
        Set Crypto = CreateObject(Security & Provider(T))
        Hash = Crypto.ComputeHash_2(Encode.GetBytes_4(Text))
        For n = LBound(Hash) To UBound(Hash)
            Response = Response & Hex(Hash(n) \ 16) & Hex(Hash(n) Mod 16)
        Next
    Else
        Response = "INVALID TYPE"
    End If
    HashString = Response
End Function

To use it, you would use something like this in a cell:

=HashString(A1 & B1, 1)

In this usage, I'm assuming that the person's name is in A1 and their address is in B1. This comprises the first parameter, which is the "seed" of which I spoke. As long as the seed is unique (regardless of the length), then the hash that is generated will be both unique and secure. The second parameter is either 1, 2, or 3, depending on the type of algorithm you want to use to generate the hash. (The code for the macro shows the values stored in the Provider() array so that you can see the algorithm.) Depending on whether you use 1, 2, or 3, the routine will return 32, 40, or 64 characters that can be used as your unique ID number.

If the code doesn't work for you, then you'll need to configure Windows so that the proper developer's framework is available to Excel. (The System.Text.UTF8Encoding object reliest on the .NET framework from Microsoft.) To enable it, follow these steps:

  1. Get out of Excel (and, for good measure, all other applications)
  2. Press the Windows key and type "windows features." Windows shows you some options.
  3. Select Turn Windows Features On and Off from the search results.
  4. Click the check box for .NET Framework 3.5.
  5. Click OK.

At this point Windows installs the .NET Framework. If necessary, it will download it to your system in order to install it. When installation is complete, you may be prompted to restart your system. When you get back into Excel, the macro should work properly.

Overkill, right? Well, you can go one better and generate a GUID, which is a much longer ID number that is vanishingly unlikely to be duplicated—for all intents and purposes, a random, unique ID number. This would be particularly helpful if you are generating IDs for use in a large organization across multiple locations worldwide. (I say this knowing, full well, that operations of this size would be unlikely to keep track of the IDs in Excel, opting, instead, for dedicated database applications.) You can find info on how to generate GUIDs within Excel by checking out this article from Chip Pearson's site:

http://www.cpearson.com/Excel/CreateGUID.aspx

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13921) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Recovering Macros and AutoText Entries from Normal.dot

Many of your custom configurations of Word—most notably macros and AutoText entries—are stored in the ...

Discover More

Erasing Table Lines

When creating tables, Word provides a handy tool that you can use. Once the table is in place, you can use the table ...

Discover More

Changing the Default Vertical Alignment

By default, Excel vertically aligns cell contents to the bottom of cells. If you prefer a different default alignment, ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Understanding Scope for Named Ranges

When you add a named range to a worksheet, you can specify if you want that named range to apply to the workbook or only ...

Discover More

Cell Address of a Maximum Value

Finding the maximum value in a range of cells is easy; finding the address of the cell containing that value is a ...

Discover More

Simulating Alt+Enter in a Formula

You can use the Alt+Enter keyboard shortcut while entering information in order to force your data onto multiple lines in ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 7?

2024-05-16 14:44:25

J. Woolley

My Excel Toolbox includes the following hashing functions:
=CRC32_CalcStr(Text)
returns the 32-bit Cyclic Redundancy Check (CRC) of Text as an 8-character Hexadecimal string, where Text is any text string (case sensitive).
=CRC32_CalcFile(File, [BufferSize])
returns the 32-bit CRC of File as an 8-character Hexadecimal string, where File is a file's name with absolute path or relative to Excel's default path. Default BufferSize (as Long) is 32768 (bytes).
=HashThisText(Text, [Method], [Base64])
returns a hash value for Text using hashing algorithm Method, where Text is any text string (case sensitive) and Method is "MD5" or "SHA1" or "SHA2-256" (default) or "SHA2-384" or "SHA2-512" (case ignored, quotation marks required). The result is a single Hexadecimal string if Base64 is FALSE (default); otherwise, it is Base64 binary-to-text encoded.
=HashThisFile([File], [Method], [Base64])
returns a hash value for File using hashing algorithm Method. File's default value is the formula cell's workbook; otherwise, provide a file name with absolute path or with path relative to the workbook or to Excel's default path. Method and Base64 are described above.
See https://sites.google.com/view/MyExcelToolbox


2024-05-04 10:30:24

J. Woolley

My Excel Toolbox includes Public Function GUID_String() which returns the Globally Unique Identifier (GUID) String that is described by Chip Pearson and referenced by the Tip. Here is an example:
991BFFE2-0636-49E7-BCFC-DE56DA3AE797
See https://sites.google.com/view/MyExcelToolbox


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.