In this article, we will learn How to increase the number in a text string in Excel.
For Instance, you have a large list of items and you need to increase the last number of the text of the old text in excel.
For this article we will be needing the use the following functions:
The TEXT function in Excel is used to convert numbers into text. The fun part is you can format that number to show it in desired format.
|= TEXT ( text , text_format )|
Text : text, in quotes or using cell reference
Text_format : the desired format
The RIGHT function in excel is used to extract text from right of a string.
|= RIGHT ( Text , [number of characters] )|
Text: The text from which you want to extract some text from right.
[number of characters]: The number of text from right you want to extract.
It is optional. By default it is 1.
Now we will make a formula out of the above functions. Here we are given the data and we needed to increase the number in a given list of values.
|= "Definite_text" & TEXT ( RIGHT ( text , 3 ) + increment , "000" )|
Let's test this formula via running it on an example.
Here we have Items_list from A2 : A. we need to increase the list number in the list using the formula.
Use the Formula:
|= "Items_" & TEXT ( RIGHT ( A2 , 3 ) + B2 , "000" )|
Now use the formula in the cell to get the new list.
As you can see in the above snapshot the argument to the function is given as cell reference.
As you can see in the above snapshot the Formula returns the new Item in the new list. Now copy the formula to other cells using the Ctrl + D shortcut or drop down cell option in excel.
As you can see the new list have the increment Items. See below some of the observational notes.
Hope this article about how Increment a number in a text string in excel is explanatory. Find more articles on SUMPRODUCT functions here. Please share your query below in the comment box. We will assist you.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.