Generate a Four-Digit Number with Unique Digits in Excel for Mac
Question details
Generate a random four-digit number in Excel without repeating any digit.
- Product
- Microsoft Excel for Mac
- Device & OS
- macOS
- Scenario
- The user wants a worksheet cell to produce a random four-digit number such as 3842.
- Observed behavior
- The result should have four digits, start with a nonzero digit, and contain no repeated digits.
Open the workbook in Excel for Mac and check that you can access the Visual Basic Editor from the Tools menu. You will need to save the workbook in the macro-enabled .xlsm format to retain a VBA function.
Create a VBA function for unique four-digit numbers
Use a custom worksheet function when you want Excel to generate a valid number in one cell.
The function generates a number from 1000 through 9999 and checks its digits. If any digit is repeated, it tries again.
In Excel for Mac, open the workbook and choose Tools > Macro > Visual Basic Editor.
In the Visual Basic Editor, select the workbook in the project pane, then choose Insert > Module.
Paste this code into the module: Function GenUniqueRand() As Long Static seeded As Boolean Dim n As Long, digits As String Dim i As Long, j As Long Dim unique As Boolean Application.Volatile If Not seeded Then Randomize seeded = True End If Do n = Int(9000 * Rnd) + 1000 digits = CStr(n) unique = True For i = 1 To 3 For j = i + 1 To 4 If Mid(digits, i, 1) = Mid(digits, j, 1) Then unique = False Exit For End If Next j If Not unique Then Exit For Next i Loop Until unique GenUniqueRand = n End Function
Return to the worksheet, enter =GenUniqueRand() in a cell, and press Return. The result can change when the workbook recalculates.
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm) to retain the code.
Create a unique four-digit number in WPS Spreadsheet
Use helper cells and formulas in WPS Spreadsheet to select four different digits, then combine them into a number without a macro.
- 1. Start a spreadsheet: Open WPS Spreadsheet and create a blank workbook or select an empty area of an existing sheet.
- 2. Choose the first digit: Enter =RANDBETWEEN(1,9) in cell A1 so the number cannot start with zero.
- 3. Choose the next two digits: Enter =MID(SUBSTITUTE("0123456789",A1,""),RANDBETWEEN(1,9),1) in B1. Then enter =MID(SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,""),RANDBETWEEN(1,8),1) in C1.
- 4. Choose the final digit: Enter =MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE("0123456789",A1,""),B1,""),C1,""),RANDBETWEEN(1,7),1) in D1.
- 5. Combine the digits: Enter =VALUE(A1&B1&C1&D1) in E1. To keep the result when formulas recalculate, copy E1 and use Paste Special > Values.

Frequently Asked Questions
Can the generated number contain zero?
Yes. Zero can appear in any position except the first, so the result remains a four-digit number.
Why does the number change when Excel recalculates?
The VBA function uses Application.Volatile, so Excel can generate a new result during recalculation. To retain a result, copy the cell and choose Paste Special > Values.
Why does Excel show a name error for the function?
Check that GenUniqueRand is in a standard module in the open workbook and that the cell contains =GenUniqueRand(). If macros are blocked, enable them only for a file you trust.
Can I save the VBA function in an .xlsx file?
No. Choose File > Save As and save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain the VBA code.




