Find and Replace Multiple Values in One Cell Simultaneously

[kumara_faith]

Hi,

I have the following range in each cell:

='C:\Users\kumar_\Documents\Kumar\Work\Daily Work Files\26 Dec 2018\2017 Daily Report\Daily Report\[Alor Setar.xls]Jan'!D4

I need to change the range to as follows:

='C:\Users\kumar_\Documents\Kumar\Work\Daily Work Files\26 Dec 2018\2018 Daily Report\Daily Report\[Alor Setar.xlsx]Jan'!D4

I have more than 6,000 rows of range to make the above changes. Each row is in different cell with different range but I only need to change the characters as highlighted in bold as above. Is there any way I could make those changes simultaneously rather than changing each cell manually one by one ? If I try to find and replace one by one, the moment I make the first change, the cell shows #REF ! because it is not the right range since the second change has not been made. Appreciate any help.

 

[gaz_chops]

Try making the 1st change as

Replace = with §
do other changes, finally
Replace § with =

§ can be any unique character, it simply changes it to text from a formula

 

[kumara_faith]

Hi Gaz,

My apologies for the late reply. I am not sure how to use the solution. Where do I get the symbol you have mentioned in your post ?

 

[gaz_chops]

As I say, use any unique character, one that you know will not appear anywhere! Maybe ^ or ~

 

[kumara_faith]

Ok, got it. Thank you and appreciate it.