Excel Formula to Increment a Number After the Second Dash
Question details
The user wants to create an Excel formula that auto-increments a two-digit number located after the second dash in a custom text code (e.g., 06-B-01-A) when the formula is dragged down.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Generating a sequential list of custom product codes or identifiers by using a drag-down cell formula.
- Observed behavior
- A static text string combined with a dynamic number format is needed to sequentially generate codes like 06-B-01-A, 06-B-02-A, and 06-B-03-A.
Ensure your target column is clear of other data and take note of the exact starting cell, as row-dependent formulas rely on precise cell references to calculate the initial increment correctly.
Using the ROW and TEXT Functions
Combine static text strings with dynamic ROW calculations to increment a formatted number sequentially.
This method breaks the code into three parts: the prefix '06-B-', the dynamically incrementing two-digit number, and the suffix '-A'. By using the ROW function, the formula calculates the current position relative to the starting row and increments by one for each subsequent row.
Click on the cell where you want the first code to appear in your sequence (for example, cell A3).
Type the formula: ="06-B-"&TEXT(ROW()-ROW($A$3)+1,"00")&"-A" into the formula bar.
Press Enter to apply the formula, which will display '06-B-01-A'. Then, click the fill handle in the bottom-right corner of the cell and drag it downward to automatically generate the subsequent codes.
Alternative Incrementing Using a Relative Cell Reference
Use a simpler relative ROW reference if you prefer to base the increment on the physical row number of a separate cell.
Create and Auto-Fill Complex Formulas in WPS Spreadsheet
WPS Spreadsheet offers full compatibility with standard Excel functions, making it incredibly easy to execute complex text manipulations and auto-fill operations efficiently.
- 1. Open a Spreadsheet: Launch WPS Spreadsheet and create a new blank workbook.
- 2. Input Your Formula: Select your desired starting cell and type the custom sequence formula using standard Excel syntax.
- 3. Auto-Fill the Sequence: Press Enter, then click and drag the fill handle at the corner of the cell to automatically populate the rest of your column.
Frequently Asked Questions
Why does my formula return 00 instead of 01?
This usually happens if the starting row calculation in the ROW function evaluates to 0. Make sure the offset logic (like `ROW()-ROW($A$3)+1`) evaluates correctly for the specific starting cell you have chosen.
How do I change the formula to output a three-digit sequence?
You can adjust the format argument in the TEXT function. Change `"00"` to `"000"`. For example, `TEXT(ROW(A1), "000")` will format the sequence as 001, 002, 003.
Will this formula still work if I insert new rows later?
Yes, but it will dynamically update. Because the sequence relies on the physical row numbers, inserting a row between existing items will cause the sequence numbers below the insertion to shift and increment according to their new row positions.
Can I increment letters instead of numbers?
Yes, but it requires a different function combination. You can use the CHAR function combined with the ROW function, for example, `CHAR(64+ROW(A1))`, to return uppercase letters (A, B, C) sequentially as you fill the formula down.
