Function Problems

Generate Unique Seven-Digit Membership IDs in Excel

Steven ParkerSteven Parker Nov 14, 2026 868 views

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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the class years

Enter a two-digit class year for each member in column D, starting at D2. Do not leave year cells blank.

2
Add the ID formula

In E2, enter =TEXT(D2,"00")&TEXT(COUNTIF($D$2:D2,D2),"00000"). For class year 82, the first ID is 8200001.

3
Fill the formula down

Select E2 and drag its fill handle down through the remaining member rows. The count starts at 00001 for each class year.

4
Make assigned IDs permanent

Select the completed IDs in column E, copy them, and use Home > Paste > Values to replace the formulas with their current results.

Generate a Separate Membership ID Sequence for Each Class Year
Preserve assigned IDs: Formula results can change if rows or class years are edited or reordered. Paste the IDs as values before making such changes. Check existing IDs before assigning IDs to new members.
Create membership IDs in WPS Spreadsheet

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. 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. 2. Enter the formula: Select E2 and enter =TEXT(D2,"00")&TEXT(COUNTIF($D$2:D2,D2),"00000").
  3. 3. Fill the IDs: Drag the fill handle from E2 down through the member rows and check the generated IDs.
  4. 4. Keep the results: Copy the ID cells and use Paste Special > Values to fix the assigned IDs before changing the roster.
Create a separate five-digit sequence for each class year.Preserve leading zeros in generated IDs.Open and save Microsoft Excel XLSX files.Use familiar spreadsheet formulas and fill handles.
microsoft office alternative - wps office

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.