Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace a string contained in a link
I have a spreadsheet with numerous external links to numerous files. A
typical link would contain the following string: 8. August\[Project 1 Aug.xls]Data Tab'$L$25 I would change string to: 9. September\[Project 1 Sep.xls]Data Tab'$L$25 The constraint is that the filename also changes throughout the spreadsheet, for instance, also want the following found and replaced: OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25 OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25 etc., etc., I have tried using the following wildcard find/replace: FIND: 8. August*Aug REPLACE: 9. September*Aug But while the find works, the replace just produces a "formula you typed containes an error" message. Any pointers? I'm sure this kind of find & replace is possible in Word... Thanks, Matt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace a string contained in a link
How about just using
Edit|links|Change sources. Matt from GVA wrote: I have a spreadsheet with numerous external links to numerous files. A typical link would contain the following string: 8. August\[Project 1 Aug.xls]Data Tab'$L$25 I would change string to: 9. September\[Project 1 Sep.xls]Data Tab'$L$25 The constraint is that the filename also changes throughout the spreadsheet, for instance, also want the following found and replaced: OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25 OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25 etc., etc., I have tried using the following wildcard find/replace: FIND: 8. August*Aug REPLACE: 9. September*Aug But while the find works, the replace just produces a "formula you typed containes an error" message. Any pointers? I'm sure this kind of find & replace is possible in Word... Thanks, Matt -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace a string contained in a link
That is the solution that I am currently using. The problem is that there are
over 30 files that are linked (and growing) to this master file. Thus using the "Change Sources" solution is getting more and more time consuming (and subject to easy mistakes due to the similarity of the file names). I need a more efficient and robust solution. Perhaps VBA is the only solution? If so, I'd appreciate some pointers in this area... Matt "Dave Peterson" wrote: How about just using Edit|links|Change sources. Matt from GVA wrote: I have a spreadsheet with numerous external links to numerous files. A typical link would contain the following string: 8. August\[Project 1 Aug.xls]Data Tab'$L$25 I would change string to: 9. September\[Project 1 Sep.xls]Data Tab'$L$25 The constraint is that the filename also changes throughout the spreadsheet, for instance, also want the following found and replaced: OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25 OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25 etc., etc., I have tried using the following wildcard find/replace: FIND: 8. August*Aug REPLACE: 9. September*Aug But while the find works, the replace just produces a "formula you typed containes an error" message. Any pointers? I'm sure this kind of find & replace is possible in Word... Thanks, Matt -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find & Replace a string contained in a link
I can't think anything more robust than Edit|links|change links.
If you did edit|replaces, you could make a typo in the "to" field and each of the formulas with errors would cause you to have to dismiss a dialog box. Maybe you could automate that edit|links|change links procedure. Matt from GVA wrote: That is the solution that I am currently using. The problem is that there are over 30 files that are linked (and growing) to this master file. Thus using the "Change Sources" solution is getting more and more time consuming (and subject to easy mistakes due to the similarity of the file names). I need a more efficient and robust solution. Perhaps VBA is the only solution? If so, I'd appreciate some pointers in this area... Matt "Dave Peterson" wrote: How about just using Edit|links|Change sources. Matt from GVA wrote: I have a spreadsheet with numerous external links to numerous files. A typical link would contain the following string: 8. August\[Project 1 Aug.xls]Data Tab'$L$25 I would change string to: 9. September\[Project 1 Sep.xls]Data Tab'$L$25 The constraint is that the filename also changes throughout the spreadsheet, for instance, also want the following found and replaced: OLD:8. August\[Project 2 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 2 Sep.xls]Data Tab'$L$25 OLD:8. August\[Project 3 Aug.xls]Data Tab'$L$25 NEW:9. September\[Project 3 Sep.xls]Data Tab'$L$25 etc., etc., I have tried using the following wildcard find/replace: FIND: 8. August*Aug REPLACE: 9. September*Aug But while the find works, the replace just produces a "formula you typed containes an error" message. Any pointers? I'm sure this kind of find & replace is possible in Word... Thanks, Matt -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to cancel a find & replace command "midstream"? | Excel Worksheet Functions | |||
Find & Replace a link to another worksheet in the same workbook | Excel Worksheet Functions | |||
Find & Replace text format | Excel Discussion (Misc queries) | |||
find and replace, within workbook | Excel Discussion (Misc queries) | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |