Explanation
The ROW function, when entered into a cell with no arguments returns the row number of that cell. In this case, the first instance of the formula is in cell D6 so, ROW() returns 6 inside the formula in D6.
We want to start with 1, however, so we need to subtract 5, which yields 1.
As the formula is copied down column D, ROW() keeps returning the current row number, and we keep subtracting 5 to “normalize” the result back to a 1-based scale:
=$B$6*1 // D6
=$B$6*2 // D7
=$B$6*3 // D8
etc
If you are copying a formula across columns, you can use the COLUMN function the same way.
Note that you are free to use the result of COLUMN or ROW any way you like in the formula. Both functions return a number, so you can apply them in a formula them just like you would use any number.
Explanation
At the core, this formula extracts the number, adds the increment, and joins the number to the original text in the right format.
Working from the inside out, this formula first extracts the numeric portion of the string in column B using the RIGHT function:
RIGHT(B5,3) // returns "001"
The result returned is actually text like “001”, “003”, etc. but when we add the numeric value from C, Excel automatically changes the next to a number and performs the addition:
RIGHT(B5,3)+C5 // returns 2
Next, this numeric result goes into the TEXT function as the value, with a number format of “000”. This pads the number with zeros as needed:
TEXT(2,"000") // returns "002"
Finally, this text string is joined to the text “Item " using concatenation:
="Item "&TEXT(2,"000")
Which returns a final result of “Item 002”.
