In the example shown, the formula in D5 is: This formula increments the number in column B by the value in column C, and outputs a string in the original format. Working from the inside out, this formula first extracts the numeric portion of the string in column B using the RIGHT function: 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: 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: Finally, this text string is joined to the text “Item " using concatenation: Which returns a final result of “Item 002”.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
