Excel - Replacing repeating instances of a text string with a series of numbers incremented by 1 each time.

Question

In Excel I would like to find all instances of a string and replace the first one with a given number (say 300) then replace each successive instance of the string with the last used number + 1.  The first instance would be replaced with 300, second would be replaced with 301, third would be replaced with 302 and so forth.

Answer 1

Hi AUTOMATIONGUYRD!

I am Jun an Independent Advisor. Thank you for reaching out to the community. To be able assist you better on this task may i ask if there is a single string on a cell or are there a group of string?

Awaiting reply

-jun

Answer 2

Throughout the spreadsheet there are multiple cells with iii in them.  The three i's are the only contents of the cell.  These are in the same column but are separated by varying numbers of rows.   I would like to be able to specify a number and the first instance of iii be replaced with that number and subsequent instances of iii be replaced with n+1, and so forth.  Think of it as serial numbers, each incremented by 1.

Answer 3

AutomationGuyRD,

Thanks for your prompt reply. Please use the following code if you want to use macro for the task:

Sub Macro1()
Dim r As Range,cell As Range,increment As Long

   Set r=Range("A:A")  

   increment=300  

   For Each cell In r  

           If cell.value="iii" Then  
              cell.value=increment  
              increment=increment+1  
           End if  
  
   Next  

End Sub

Note: This is assuming that you have the data on column A and you want to replace "iii" with "300"

-jun

Answer 4

The other option is to use the replace function.

1. change the first "iii" to "300" in the column
2. go to file>options>formulas>check the "R1C1 reference" option
3. select the column
4. open the replace dialog box (ctrl+H)
5. type "iii" in the replace
6. in the "replace with" type in "=max(R1C1:R[-1]C)+1)"
7. you can copy and paste special the values if you want
8. uncheck "R1C1 reference"

Hope these solutions will give you your intended output. If you'll be needing further assistance please don't hesitate to reply. Keep safe!

-jun

Answer 5

Hi 

Please, try this macro

Note: This is a dynamic solution 

Let's say the strings to be replaced are in column "B" Then

In cell A1, type the search criteria (ex. "iii")  in Yellow

In cell A2, type the starting number  (300)  in Green

and run the macro with the help of a button

Change the ranges according to your real scenario.

AFTER

BEFORE

Here is the code

***************************************************************************************

Sub ReplaceStringwithNumbers()

Dim myRange As Range, str As Range

Dim myString As Variant, myNumber As Long

With ActiveSheet

        myString = Range("A1").Value   ''' the search string

        myNumber = Range("A2").Value  ''' the starting number to replace

        Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))  ''' the searching column range

        For Each str In myRange

            If InStr(1, str, myString, vbTextCompare) > 0 Then

                    str.Value = Replace(str.Value, myString, myNumber)

                    myNumber = myNumber + 1  '' number increment

           End If

        Next str

End With

MsgBox "Job Done"

End Sub

************************************************************************************************

I hope this helps you and solve your problem

Regards

Jeovany