Generate Unique Seven-Digit Membership IDs in Excel
Question details
Create membership IDs with a two-digit class year followed by a unique five-digit sequence.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A membership roster needs an ID for each member, either with one fixed class-year prefix or a separate sequence for each class year.
- Observed behavior
- Each ID should contain seven digits, with leading zeros preserved in the year and sequence.
Place each member's two-digit class year in column D, starting at D2, and use a consistent format for all year entries. Ensure that each class year has no more than 99,999 members and that two different centuries do not share the same two-digit year.
Generate a Separate Membership ID Sequence for Each Class Year
Use COUNTIF when the roster contains multiple class years and each year needs its own sequence.
Enter a two-digit class year for each member in column D, starting at D2. Do not leave year cells blank.
In E2, enter =TEXT(D2,"00")&TEXT(COUNTIF($D$2:D2,D2),"00000"). For class year 82, the first ID is 8200001.
Select E2 and drag its fill handle down through the remaining member rows. The count starts at 00001 for each class year.
Select the completed IDs in column E, copy them, and use Home > Paste > Values to replace the formulas with their current results.

Generate Sequential IDs with One Fixed Class-Year Prefix
Use ROW when every member in the list has the same two-digit class year.
Generate Membership IDs in WPS Spreadsheet
Use TEXT and COUNTIF in WPS Spreadsheet to combine each class year with a five-digit membership sequence, then save the results as values.
- 1. Open the roster: Open your Excel workbook in WPS Spreadsheet and place the two-digit class years in column D, starting at D2.
- 2. Enter the formula: Select E2 and enter =TEXT(D2,"00")&TEXT(COUNTIF($D$2:D2,D2),"00000").
- 3. Fill the IDs: Drag the fill handle from E2 down through the member rows and check the generated IDs.
- 4. Keep the results: Copy the ID cells and use Paste Special > Values to fix the assigned IDs before changing the roster.

Frequently Asked Questions
How do I display a hyphen between the year and sequence?
In E2, use =TEXT(D2,"00")&"-"&TEXT(COUNTIF($D$2:D2,D2),"00000"). The result contains seven digits plus a hyphen, such as 82-00001.
Will the formula preserve a class year such as 08?
Yes. TEXT(D2,"00") displays a numeric year of 8 as 08. Enter class years consistently throughout column D.
Why might an assigned ID change when I sort the roster?
The COUNTIF formula counts occurrences in row order, so reordering rows can change its results. Before sorting, copy the ID column and use Home > Paste > Values.
How do I assign an ID to a new member after pasting IDs as values?
Check the existing IDs for that class year, then enter the next unused five-digit sequence with the same year prefix. Avoid restarting at 00001 for a year that already has members.




