Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,722
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 28
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wildcard in the Replace function User 100 Excel Discussion (Misc queries) 2 November 25th 09 05:14 AM
Use of Wildcard characters with replace tonuab Excel Discussion (Misc queries) 2 November 6th 09 10:39 PM
Doing a replace with a wildcard charcter John Excel Discussion (Misc queries) 2 October 2nd 09 02:49 PM
replace using wildcard, or else Office 2003 lover Excel Discussion (Misc queries) 3 January 17th 09 06:52 AM
Find Replace Wildcard dk New Users to Excel 9 September 23rd 08 03:45 AM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"