Increment a number in a text string in excel

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:

  1. TEXT Function
  2. RIGHT Function

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.

Syntax:

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

Syntax:

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

Generic formula:

= "Definite_text" & TEXT ( RIGHT ( text , 3 ) + increment , "000" )

Explanation:

  • Here RIGHT ( text , 3 ) picks three characters from the right of the text.
  •  + increment increases the number from the value.
  • TEXT ( number, "000") format the number to 000 format. 
  • & operator joins the two texts and returns the new text.

Example:

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

Explanation:

  • Here RIGHT ( A2 , 3 ) picks three characters from the right of the text in A2 and which is 001.
  •  + increment increases the number from the value. 001 + 2 = 3
  • TEXT ( 3, "000") format the number to 003 text format. 
  • & operator joins the two text values and returns the new text Items_003.

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. 

Notes:

  1. The formula returns #VALUE! error if the argument to the function is numeric.
  2. Definite text must be correct.
  3. The function returns name error if text to the function is not provided with using the quote sign ( " ).
  4. The text and increment number can be given as cell reference.
  5. The number format can be used as choice.
  6. Increment argument must be numeric.
  7. The formula returns wrong result if the formula syntax is not correct.
  8. The text_format argument to the TEXT function should be given as cell reference.

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.

Related Articles

How to use the TEXT function in excel

How to use the RIGHT function in excel

Validation of text entries

Create drop down list in excel with colour

Remove leading and trailing spaces from text in Excel

Popular Articles

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Vlookup by date

Join first and last name in excel

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube