Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a wildcard within edit/replace
Hello
Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) ,"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D: $E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImpor t!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefore I should like the row below to be amended from $A9 to $C9 and so on. I can't see how to do this as I need to change the formula either side of the cell reference and therefore doing this in 2 parts, which at first I thought I could, would mean the formula not working in the interim (and therefore edit/replace not working). I also tried to use a wildcard in place of the row reference but this didn't seem to work either. Any help is appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a wildcard within edit/replace
You could use Find & Replace to change = to xx=, thereby converting
all the formulae to text. Then apply your own changes and at the end change xx= back to =. Hope this helps. Pete On Dec 8, 2:37*pm, BabyMc wrote: Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) ,"",VLOOKUP($A8,DataImpor*t!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D: $E,2,FALSE)),"",VLOOKUP(T*EXT($C8,"0000"),DataImpo rt!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefore I should like the row below to be amended from $A9 to $C9 and so on. I can't see how to do this as I need to change the formula either side of the cell reference and therefore doing this in 2 parts, which at first I thought I could, would mean the formula not working in the interim (and therefore edit/replace not working). I also tried to use a wildcard in place of the row reference but this didn't seem to work either. Any help is appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a wildcard within edit/replace
I believe this order of replacements will work. All find/replace combinations
should be entrered w/o outside quotation marks. Find "=" Replace "zzz=" Find "$A:$J,5" Replace "$D:$E,2" Find "($A" Replace "(TEXT($C" Find ",Data" Replace ","0000"),Data" Find "zzz=" Replace "=" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "BabyMc" wrote: Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) ,"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D: $E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImpor t!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefore I should like the row below to be amended from $A9 to $C9 and so on. I can't see how to do this as I need to change the formula either side of the cell reference and therefore doing this in 2 parts, which at first I thought I could, would mean the formula not working in the interim (and therefore edit/replace not working). I also tried to use a wildcard in place of the row reference but this didn't seem to work either. Any help is appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a wildcard within edit/replace
Luke
Thanks for your help. However I was sort of hoping there may be a single solution to my problem. I guess I'd worked out a similar solution to yours but I have about 100 workbooks in which I would like to do this so was hoping to reduce the amount of times to use the edit/replace function. Havign said that if there is no other way then I would just have to go with something like this. Thanks again. "Luke M" wrote: I believe this order of replacements will work. All find/replace combinations should be entrered w/o outside quotation marks. Find "=" Replace "zzz=" Find "$A:$J,5" Replace "$D:$E,2" Find "($A" Replace "(TEXT($C" Find ",Data" Replace ","0000"),Data" Find "zzz=" Replace "=" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "BabyMc" wrote: Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) ,"",VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D: $E,2,FALSE)),"",VLOOKUP(TEXT($C8,"0000"),DataImpor t!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefore I should like the row below to be amended from $A9 to $C9 and so on. I can't see how to do this as I need to change the formula either side of the cell reference and therefore doing this in 2 parts, which at first I thought I could, would mean the formula not working in the interim (and therefore edit/replace not working). I also tried to use a wildcard in place of the row reference but this didn't seem to work either. Any help is appreciated. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use a wildcard within edit/replace
Thanks again
However because these formulae are present in many rows I still think using your method requires edit/replace to be used four times (to convert to text, amend the formula before the row number, amend the formula after the row number, and finally to convert back to a formula). I was hopeful that there may be a solution so that I can use edit/replace just the once. Or am I missing something? "Pete_UK" wrote: You could use Find & Replace to change = to xx=, thereby converting all the formulae to text. Then apply your own changes and at the end change xx= back to =. Hope this helps. Pete On Dec 8, 2:37 pm, BabyMc wrote: Hello Is there a method of using a wildcard function within edit/replace (in Excel 2003) so that I can tweak the way a formula works? The example I have is the following formula =IF(ISERROR(VLOOKUP($A8,DataImport!$A:$J,5,FALSE)) ,"",VLOOKUP($A8,DataImporĀ*t!$A:$J,5,FALSE)) which I would like to change to =IF(ISERROR(VLOOKUP(TEXT($C8,"0000),DataImport!$D: $E,2,FALSE)),"",VLOOKUP(TĀ*EXT($C8,"0000"),DataImp ort!$D:$E,2,FALSE)) The issue is (I think) that this formula is repeated many times over in one column over a number of worksheets - therefore I should like the row below to be amended from $A9 to $C9 and so on. I can't see how to do this as I need to change the formula either side of the cell reference and therefore doing this in 2 parts, which at first I thought I could, would mean the formula not working in the interim (and therefore edit/replace not working). I also tried to use a wildcard in place of the row reference but this didn't seem to work either. Any help is appreciated. Thanks . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard in the Replace function | Excel Discussion (Misc queries) | |||
Use of Wildcard characters with replace | Excel Discussion (Misc queries) | |||
Doing a replace with a wildcard charcter | Excel Discussion (Misc queries) | |||
replace using wildcard, or else | Excel Discussion (Misc queries) | |||
Find Replace Wildcard | New Users to Excel |