How to Generate Alphanumeric Sequences (1a, 1b, 1c) in Excel
Question details
The user needs to create an auto-filling sequence combining numbers and letters (e.g., 1a, 1b, 1c) rather than standard decimal formatting.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating specific multi-level numbering formats for lists, outlines, or tracking IDs in a spreadsheet.
- Observed behavior
- When attempting to use the fill handle, Excel defaults to generating decimal values (like 1.1, 1.2) or simply copies the text instead of incrementing the alphanumeric sequence.
Determine whether you need a permanent sequence that you will use frequently across multiple files (best for Custom Lists) or a dynamic list that auto-updates for a specific document (best for Formulas).
Use an Advanced Formula to Auto-Fill the Sequence
This dynamic method is the most robust way to automatically generate a continuous sequence like 1a through 1z, and automatically transition to 2a through 2z without manual typing.
This formula uses the ROW function to count rows, the ROUNDUP function to determine the number prefix, and the CHAR function to convert character codes into lowercase letters (where ASCII 97 is 'a').
Click on the cell where you want your sequence to begin (for example, cell A1).
Type the following formula exactly as shown: =ROUNDUP(ROW(1:1)/26,0)&CHAR(IF(MOD(ROW(1:1),26)=0,26,MOD(ROW(1:1),26))+96)
Press Enter to generate the value '1a'. Then, click and hold the fill handle (the small green square at the bottom-right corner of the cell) and drag it down the column to generate 1b, 1c, and so on.
Create an Excel Custom List for Frequent Use
Ideal if you frequently use the exact same alphanumeric sequence across different workbooks and want Excel to natively recognize the pattern.
Use a Helper Column with Simple Concatenation
A simpler workaround if you only need a quick alphanumeric list without complex mathematical formulas.
Easily Create Alphanumeric Sequences with WPS Spreadsheet
WPS Spreadsheet fully supports advanced formulas, custom lists, and data manipulation to help you generate alphanumeric sequences like 1a, 1b, and 1c effortlessly. It processes Excel formulas natively, ensuring a smooth transition for all your workflow needs.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
- 2. Apply the Sequence Formula: Select your starting cell and paste the sequence formula: =ROUNDUP(ROW(1:1)/26,0)&CHAR(IF(MOD(ROW(1:1),26)=0,26,MOD(ROW(1:1),26))+96)
- 3. Drag to Auto-Fill: Press Enter, grab the fill handle in the lower right corner of the cell, and drag down to instantly populate your custom alphanumeric list.
Frequently Asked Questions
Can I generate a sequence like A1, A2, A3 instead?
Yes. This is much easier as Excel natively understands this format. Simply type A1 in a cell, press Enter, and drag the fill handle down. Excel will automatically generate A2, A3, and so on without the need for complex formulas.
Why does the fill handle just copy '1a' instead of creating a sequence?
Excel's default AutoFill logic does not automatically increment a number if it is attached to text at the end of the string. You either need to set up a Custom List in File > Options > Advanced > Edit Custom Lists, or use the advanced formula method.
How do I edit or delete a Custom List I created?
Go to File > Options > Advanced and click on Edit Custom Lists under the General section. Select the list you want to modify from the Custom lists box, and then click the Delete button or modify the entries in the List entries box.
