Trying to substitute old text for new text

[lbd518]

Hi all,
I have a series of codes (e.g., NAC4, NAC5, NAC6) embedded in paragraphs (one paragraph per cell) that need to be updated with new codes (e.g., NAC4 needs to be replaced with NAC35). I'm using a "Substitute Multiple" formula, which seems to work when the codes end in single digits (e.g., NAC4 is converted to NAC35 without issue), but when the code ends in two or more digits, I get what appear to be a string of random numbers (e.g., NAC20 becomes NAC3430 instead of NAC51). I'm guessing the numbers are, in fact, not random, but Excel is substituting and then re-substituting as it progresses through the text. Is it possible to alter the formula so that this doesn't happen? For reference, here's the VBA code for the formula:

'Name function and dimension argument variables and declare their data types
Function SubstituteMultiple(text As String, old_text As Range, new_text As Range)

'Dimension variable and declare data type
Dim i As Single

'Iterate through cells in argument old_text
For i = 1 To old_text.Cells.Count

'Replace strings in value based on variable i
Result = Replace(text, old_text.Cells(i), new_text.Cells(i))

'Save manipulated value to variable text
text = Result
Next i

'Return value stored in variable Result to worksheet
SubstituteMultiple = Result
End Function

 

[Peter_SSs]

Welcome to the MrExcel board!

It would be very helpful to see some representative dummy sample data, substitution tables that I gather you have, and the expected results, preferably with XL2BB so that we can easily copy/paste to test with.

However, I think you are going to need something more robust. This may be what is already happening to you but even if you had a single substitution of replacing NAC4 to NAC35 but your original text contained NAC48 it would become NAC358.

I think that we can get around that problem but the representative sample data and results will help, particularly in identifying if there is punctuation of any kind in the data & generally what the data text might look like.

It may not be particularly relevant to this exercise but I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[maabadi]

I think you should test Reverse Method. and Replace from highest to lowest.
Please check this at your code:
Change:

For i = 1 To old_text.Cells.Count

to

For i = old_text.Cells.Count To 1 Step -1