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

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

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

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

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

.