| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using wild card characters in Find & Replace
Hi,
Below a previous posted problem not solved yet ! Maybe somebody can help ! Thanks, To explain the problem better: Have 3 workbooks with multiple sheets which is consolidated into 1 workbook. The links to the 3 workbooks had to be changed and was done with the "data, edit links, change source" command but one of the the links was mistakenly duplicated resulting in the info below. File was saved so can not undo. Formula: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 Want to change it to: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 Want to do this for the whole workbook iso doing it one by one since it is has approx 2000 links that follow the same logic, with only the file name to be changed. The rest below might make more sense now ! "Tola" wrote: Hi Luke, I tried the following: Find: +'[BBB.xls]?????????? Replace with: +'[CCC.xls]?????????? Message: Excel found no data to replace The formula contains a link to the one file twice and I want to replace one of the links with another link. Therfor can not use Data,edit links, change source. Can do what you suggest but means I will have to do so cell by cell and the change in the link is required in multiple cells on multiple sheets ! "Luke M" wrote: They are recognized, but you may not be applying them correctly. Also, if you search for a wildcard, you'd have to define what to replace that wildcard with! Perhaps you can do: Find: +'[BBB.xls]Sheet1'!A7+ Replace with: +'[CCC.xls]Sheet1'!A7+ -- Best Regards, Luke M "Tola" wrote in message ... How do I change a range of Formula linked to 3 other files ? Example: Cell Reference: A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8 Want to change one of the references to '[BBB.xls] to '[CCC.xls] using wildcard characters but * and ? does not seam to be regognized in find and repalce ? . Was this post helpful to you? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using wild card characters in Find & Replace
Hi
Replace '[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 With '[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 -- Regards Roger Govier Tola wrote: Hi, Below a previous posted problem not solved yet ! Maybe somebody can help ! Thanks, To explain the problem better: Have 3 workbooks with multiple sheets which is consolidated into 1 workbook. The links to the 3 workbooks had to be changed and was done with the "data, edit links, change source" command but one of the the links was mistakenly duplicated resulting in the info below. File was saved so can not undo. Formula: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 Want to change it to: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 Want to do this for the whole workbook iso doing it one by one since it is has approx 2000 links that follow the same logic, with only the file name to be changed. The rest below might make more sense now ! "Tola" wrote: Hi Luke, I tried the following: Find: +'[BBB.xls]?????????? Replace with: +'[CCC.xls]?????????? Message: Excel found no data to replace The formula contains a link to the one file twice and I want to replace one of the links with another link. Therfor can not use Data,edit links, change source. Can do what you suggest but means I will have to do so cell by cell and the change in the link is required in multiple cells on multiple sheets ! "Luke M" wrote: They are recognized, but you may not be applying them correctly. Also, if you search for a wildcard, you'd have to define what to replace that wildcard with! Perhaps you can do: Find: +'[BBB.xls]Sheet1'!A7+ Replace with: +'[CCC.xls]Sheet1'!A7+ -- Best Regards, Luke M "Tola" wrote in message ... How do I change a range of Formula linked to 3 other files ? Example: Cell Reference: A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8 Want to change one of the references to '[BBB.xls] to '[CCC.xls] using wildcard characters but * and ? does not seam to be regognized in find and repalce ? . Was this post helpful to you? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using wild card characters in Find & Replace
Sorry - ignore my post.
Didn't read the question properly -- Regards Roger Govier Roger Govier wrote: Hi Replace '[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 With '[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 -- Regards Roger Govier Tola wrote: Hi, Below a previous posted problem not solved yet ! Maybe somebody can help ! Thanks, To explain the problem better: Have 3 workbooks with multiple sheets which is consolidated into 1 workbook. The links to the 3 workbooks had to be changed and was done with the "data, edit links, change source" command but one of the the links was mistakenly duplicated resulting in the info below. File was saved so can not undo. Formula: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 Want to change it to: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 Want to do this for the whole workbook iso doing it one by one since it is has approx 2000 links that follow the same logic, with only the file name to be changed. The rest below might make more sense now ! "Tola" wrote: Hi Luke, I tried the following: Find: +'[BBB.xls]?????????? Replace with: +'[CCC.xls]?????????? Message: Excel found no data to replace The formula contains a link to the one file twice and I want to replace one of the links with another link. Therfor can not use Data,edit links, change source. Can do what you suggest but means I will have to do so cell by cell and the change in the link is required in multiple cells on multiple sheets ! "Luke M" wrote: They are recognized, but you may not be applying them correctly. Also, if you search for a wildcard, you'd have to define what to replace that wildcard with! Perhaps you can do: Find: +'[BBB.xls]Sheet1'!A7+ Replace with: +'[CCC.xls]Sheet1'!A7+ -- Best Regards, Luke M "Tola" wrote in message ... How do I change a range of Formula linked to 3 other files ? Example: Cell Reference: A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8 Want to change one of the references to '[BBB.xls] to '[CCC.xls] using wildcard characters but * and ? does not seam to be regognized in find and repalce ? . Was this post helpful to you? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Using wild card characters in Find & Replace
Hi
Use the following piece of VBA code Sub ChangeFile() Dim c As Range Dim x As Variant For Each c In Selection x = Split(c.Formula, "+") x(2) = Replace(x(2), "BBB", "CCC") c.Formula = Join(x, "+") Next End Sub To install Copy code above Alt+F11 to invoke VB Editor Alt+I+M to insert Module Paste code into white pane that appears Alt+F11 to return to Excel To Use Select the column of cells which contain the wrong formula Alt+F8 to bring up the macro dialogue select ChangeFile Run Repeat for each of the sheets that you need to change -- Regards Roger Govier Tola wrote: Hi, Below a previous posted problem not solved yet ! Maybe somebody can help ! Thanks, To explain the problem better: Have 3 workbooks with multiple sheets which is consolidated into 1 workbook. The links to the 3 workbooks had to be changed and was done with the "data, edit links, change source" command but one of the the links was mistakenly duplicated resulting in the info below. File was saved so can not undo. Formula: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 Want to change it to: :='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[CCC.xls]Sheet1'!A7 Want to do this for the whole workbook iso doing it one by one since it is has approx 2000 links that follow the same logic, with only the file name to be changed. The rest below might make more sense now ! "Tola" wrote: Hi Luke, I tried the following: Find: +'[BBB.xls]?????????? Replace with: +'[CCC.xls]?????????? Message: Excel found no data to replace The formula contains a link to the one file twice and I want to replace one of the links with another link. Therfor can not use Data,edit links, change source. Can do what you suggest but means I will have to do so cell by cell and the change in the link is required in multiple cells on multiple sheets ! "Luke M" wrote: They are recognized, but you may not be applying them correctly. Also, if you search for a wildcard, you'd have to define what to replace that wildcard with! Perhaps you can do: Find: +'[BBB.xls]Sheet1'!A7+ Replace with: +'[CCC.xls]Sheet1'!A7+ -- Best Regards, Luke M "Tola" wrote in message ... How do I change a range of Formula linked to 3 other files ? Example: Cell Reference: A7:='[AAA.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7+'[BBB.xls]Sheet1'!A7 A8:='[AAA.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8+'[BBB.xls]Sheet1'!A8 Want to change one of the references to '[BBB.xls] to '[CCC.xls] using wildcard characters but * and ? does not seam to be regognized in find and repalce ? . Was this post helpful to you? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find and Replace with wild cards | Excel Discussion (Misc queries) | |||
| Replacing wild card characters in EXCEL | Excel Discussion (Misc queries) | |||
| Countif using Wild Card Characters | Excel Worksheet Functions | |||
| Wild Cards in Find and Replace | Excel Discussion (Misc queries) | |||
| Using wild card characters in array formulas | Excel Worksheet Functions | |||
Linear Mode
