Trying to substitute old text for new text

lbd518

New Member
Joined
Dec 13, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,673
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
I think you should test Reverse Method. and Replace from highest to lowest.
Please check this at your code:
Change:
VBA Code:
For i = 1 To old_text.Cells.Count
to
VBA Code:
For i = old_text.Cells.Count To 1 Step -1
 

Watch MrExcel Video

Forum statistics

Threads
1,128,020
Messages
5,628,182
Members
416,299
Latest member
arunvistas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top