Formula Errors

How to Create an Incrementing Alphanumeric Sequence in Excel

Guest Writer Oct 10, 2026 869 views

Question details

The user wants to generate sequential alphanumeric identifiers containing a fixed prefix, an incrementing number, and an optional suffix.

How to Create an Incrementing Alphanumeric Sequence in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Generating unique identifiers, invoice numbers, or tracking codes dynamically in a spreadsheet.
Observed behavior: The user needs a reliable formula to auto-generate identifiers (like Prefix-0001-Suffix) across multiple rows or linked to other worksheets without manual data entry.

Before you start

Determine the exact format of your alphanumeric sequence, including the specific prefix, suffix, and the number of digits you want the incrementing number to display (e.g., 4 digits like '0001').

Solution 1Recommended

Generate Auto-Incrementing Sequences Using TEXT and ROWS

Use this method to automatically increment the numeric portion of your string as you drag the fill handle down a column.

This formula combines static text (prefix/suffix) with a dynamic number generated by the ROWS function. The TEXT function forces the number to display with a specific number of leading zeros.

Select the starting cell

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

Enter the sequence formula

Type the formula ="YourPrefix"&TEXT(ROWS($1:1),"0000")&"YourSuffix" into the formula bar.

Customize your text

Replace 'YourPrefix' and 'YourSuffix' with the actual text you need. If you do not need a suffix, simply delete the &"YourSuffix" portion.

Drag the fill handle

Press Enter to apply the formula. Click and hold the small square at the bottom-right corner of the cell, and drag it down the column to fill the sequence in subsequent rows.

Generate Auto-Incrementing Sequences Using TEXT and ROWS

Formatting Leading Zeros: The '0000' in the TEXT function ensures the number always displays with four digits, like 0001, 0002. You can adjust the number of zeros to fit your specific formatting needs.

Solution 2

Create Sequences by Referencing Another Worksheet

Use this method if you have a base number on one sheet and want to generate an alphanumeric string based on it in another sheet.

Productivity Booster

Generate Alphanumeric Sequences Quickly in WPS Spreadsheet

WPS Spreadsheet is a powerful and free tool that fully supports advanced Excel formulas like TEXT and ROWS, allowing you to create complex alphanumeric sequences seamlessly.

1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to create the sequence.
2. Enter the formula: Select your starting cell and input the formula ="Prefix-"&TEXT(ROWS($1:1),"000") to set up a 3-digit incrementing code.
3. Auto-fill the sequence: Press Enter, then click and drag the fill handle at the bottom right of the cell downwards to auto-generate the remaining identifiers.

100% compatible with Microsoft Excel formulas and .xlsx file formats.Intuitive interface for easy drag-and-drop auto-filling of sequential data.Lightweight, fast, and completely free to use for everyday spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Can I increment letters instead of numbers in an alphanumeric sequence?

Yes, but it requires using the CHAR function. For example, the formula ="Prefix-"&CHAR(64+ROWS($1:1)) will return Prefix-A in the first row, Prefix-B in the second row, and so on.

Why doesn't my sequence increment when I drag the fill handle?

If you manually typed a static string like 'Prefix-0001' and dragged it, the application might just copy the identical text depending on your auto-fill settings. Using the ROWS($1:1) formula guarantees the number increments because the row reference changes dynamically.

How can I start my alphanumeric sequence at a specific number, like 100?

You can modify the formula to add your desired starting number minus 1 to the ROWS function. For instance, ="Prefix-"&TEXT(ROWS($1:1)+99,"0000") will start your sequence at Prefix-0100.

How do I remove the suffix from the formula if I only need a prefix?

Simply delete the &"YourSuffix" part at the very end of the formula. Your new formula will look like ="YourPrefix"&TEXT(ROWS($1:1),"0000").

Excel & Data Problems