Function Problems

How to Autofill Sequential Numbers with Letters in Excel

Maira Mehtab Oct 12, 2026 871 views

Question details

The user needs a reliable way to autofill a sequence of strings containing both letters and numbers while accurately preserving leading zeros.

How to Autofill Sequential Numbers with Letters in Excel

Product: Excel
Device & OS: not provided
Scenario: Generating sequential alphanumeric IDs (such as nvdspcs0670b, nvdspcs0671b, etc.) for data entry, tracking, or inventory management.
Observed behavior: The default Excel fill handle fails to consistently increment the numeric portion when the number is embedded between text characters.

Before you start

Identify the constant text components of your sequence (the prefix and suffix) and determine the exact starting number alongside the required number of leading zeros.

Solution 1Recommended

Use a Formula with TEXT and ROW Functions

This is the most reliable and automated method to increment a number embedded between text characters while preserving specific leading zeros.

By combining the TEXT function to maintain leading zeros and the ROW function to create an incrementing value, you can generate complex alphanumeric sequences that the standard fill handle struggles with.

Select the starting cell

Click on the cell where you want the first value of your sequence to appear.

Enter the sequence formula

Type the formula: ="nvdspcs"&TEXT(669+ROW(A1),"0000")&"b" into the formula bar. Replace '669' with the number exactly one less than your desired starting number (e.g., if starting at 670, use 669).

Adjust the formatting string

Ensure the "0000" part matches the total number of digits you want the numeric portion to have. This forces Excel to include leading zeros.

Drag to autofill

Press Enter, then click the small square at the bottom-right corner of the cell (the fill handle) and drag it down to fill the rest of your sequence.

Use a Formula with TEXT and ROW Functions

Formula Customization: You can easily change the prefix "nvdspcs" and the suffix "b" inside the quotation marks to fit your specific required text format.

Solution 2

Use Helper Columns and Concatenation

If you find complex formulas difficult, you can autofill the numeric sequence in a separate column first and then combine it with your text elements.

WPS Spreadsheet Solution

Easily Create Alphanumeric Sequences in WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet tool that fully supports advanced formulas, including TEXT and ROW, to help you generate complex custom sequences with just a few clicks.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to add your alphanumeric sequence.
2. Input the formula: Select the target cell and enter your custom combination formula using the TEXT and ROW functions.
3. Use the fill handle: Hover your mouse over the bottom-right corner of the active cell until the crosshair cursor appears.
4. Drag to complete: Click and drag the cursor downwards to instantly generate and autofill your sequenced IDs.

100% compatible with Microsoft Excel formulas and formattingFree and lightweight spreadsheet tool for everyday data tasksIntuitive drag-and-drop fill handle for instant sequence generationAdvanced text and data manipulation features

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the Excel fill handle work for my text and number combination?

Excel's default fill handle often struggles to identify the correct numeric increment pattern when numbers are sandwiched between text characters, or when specific leading zeros need to be maintained.

How do I keep leading zeros when dragging down a sequence?

You can wrap your numeric reference in the TEXT function. For example, using TEXT(A1, "0000") forces the generated number to display with leading zeros up to a total of four digits.

Can I autofill just a prefix and a number without a suffix?

Yes. You simply omit the suffix part from your formula. For example, using ="Prefix"&TEXT(ROW(A1),"000") will easily generate items like Prefix001, Prefix002, and so forth.

Excel & Data Problems