Function Problems

How to Generate Sequential Number-and-Letter Codes in Excel

Natalie Taylor Sep 30, 2026 868 views

Question details

The user wants to create an auto-filling sequence in Excel that combines a fixed number with sequential letters (a, b, c) and then repeated letters (aa, bb, cc).

How to Generate Sequential Number-and-Letter Codes in Excel

Product: Microsoft Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Creating custom alphanumeric sequence codes for tracking inventory, IDs, or data categorization.
Observed behavior: The user needs a formula that can be dragged down a column to automatically generate the desired sequential pattern without manual data entry.

Before you start

Ensure your sequence starts in the correct row, as these formulas rely on row references to calculate the alphabetical progression accurately.

Solution 1Recommended

Use REPT and CHAR Functions for Repeated Letter Sequences

This solution generates the exact requested pattern (1A to 1Z, then 1AA to 1ZZ) by calculating row offsets and repeating characters.

This formula works by finding the difference in row numbers to determine the current letter in the alphabet and the number of times it should be repeated.

Select the starting cell

Click on the first cell in the column where you want your sequence to begin.

Enter the sequence formula

Type or paste the following formula: ="1"&REPT(CHAR(65+MOD(ROW(A1)-ROW(A$1),26)),QUOTIENT(ROW(A1)-ROW(A$1),26)+1)

Apply the formula

Press Enter. The cell will output '1A'.

Fill the sequence down

Click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to generate the rest of the sequence, such as 1B, 1C, and eventually 1AA.

Use REPT and CHAR Functions for Repeated Letter Sequences

Lowercase conversion: If you need lowercase letters (1a, 1b, 1aa), change the '65' in the formula to '97', which is the ASCII code for lowercase 'a'.

Solution 2

Use the ADDRESS Function for Standard Column Letter Sequences

If you prefer standard Excel column naming (1A to 1Z, then 1AA to 1AZ, 1BA to 1BZ), use this alternative formula.

Try WPS Spreadsheet for Free

Generate Sequences Easily with WPS Spreadsheet

WPS Spreadsheet offers full compatibility with standard Excel functions like REPT, CHAR, and ADDRESS. You can easily generate alphanumeric sequences and automate your workflows in a lightweight, free alternative.

1. Open a new workbook: Launch WPS Spreadsheet and select a blank workbook.
2. Enter the formula: Type your sequence formula into the first cell of your target column.
3. Locate the fill handle: Hover over the bottom-right corner of the cell until the cross cursor appears.
4. Drag to automate: Drag the fill handle down to generate your number-and-letter codes seamlessly.

Fully compatible with Microsoft Excel formulas and formattingFree to use for daily data processing and sequencingLightweight application that runs smoothly on any deviceFamiliar user interface requires no learning curve

Frequently Asked Questions

Why does my sequence start with the wrong letter when I paste the formula in a different row?

The formula uses ROW(A1)-ROW(A$1) as a starting reference to calculate offsets. If you paste it into a different location, ensure the absolute reference (A$1) corresponds to the actual first row of your data so the math begins at zero.

Can I change the prefix number '1' to another text or number?

Yes, simply change the "1" at the beginning of the formula to any string you prefer. For example, using "Item-"&REPT(...) will output sequences like Item-A, Item-B, Item-AA.

How many combinations can the standard column sequence formula generate?

The ADDRESS method generates up to 16,384 combinations, matching Excel's maximum column count, ending at 1XFD.

Excel & Data Problems