Exceljet

Quick, clean, and to the point

Increment a number in a text string

Excel formula: Increment a number in a text string
Generic formula 
="Item "&TEXT(RIGHT(A1,3)+increment,"000")
Explanation 

This formula looks at one way to increment a number that is embedded in a text string. The purpose of this example to show how multiple functions can be combined to split, manipulate, and rejoin values.

In the example shown, the formula in D5 is:

="Item "&TEXT(RIGHT(B5,3)+C5,"000")

This formula increments the number in column B by the value in column C, and outputs a string in the original format.

How this formula works

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".

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.