How to combine functions in a formula

In this video I'm going to show you how you can use multiple Excel functions to split, manipulate, and rejoin values inside a single formula.

Here we have some sample data and, in column B, we have text values with a number at the end. What we want to do is increment these numbers using the value in column C.

Now if I try to do this directly, with a formula that adds C5 to B5, I'll get an error because the value in B is text, and Excel won't let you add numbers and text.

So what I need to do is extract the number, then do the math, then put things back together again. Let's do that step-by-step.

First, I'll use the RIGHT function to extract the three characters from the right. This gives us the number. You can see by the left alignment that the number is still in text format.

However, now I can add the value from column C.

This time it works because Excel is able to convert the text to a number automatically as part of the math operation. We get a correct result of 2, but we loose the padding with zeros.

So let's add the padding back using the TEXT function. The TEXT function lets us apply a number format inside a formula. I just need to wrap the existing formula in the TEXT function, and then use "000" as the number format.

OK, so now we have our incremented number padded with zeros.

The final step is to rejoin this number to the original text using simple concatenation.

To do that, I'll just hard-code the text "Item " with a space at the start of the formula; then use the ampersand to join the text to the padded number we already created.

When I hit Return we get our final result, and you can see that the number is incremented properly.

Now I can just copy the formula down the table to increment the remaining values.

When you face a problem like this, the key is to work in small steps, and verify your progress as you go. The final result may look complex, but in most cases each piece of the formula is doing something very simple.