Why not accept wildcards for REPLACE as well as FIND ?

[The Blue Max]

Why not allow matching wildcards (? and *) in the REPLACE string as well as
in the FIND string? Using the Excel FIND AND REPLACE feature is handicapped
by the inability to copy the wildcard strings from the FIND operation and
restore it as part of the REPLACE operation. Often users want to use the
wildcard characters as a template to find strings in order to replace the
common component, but do NOT want to replace the wildcard component.

For example, we want to FIND Building 1A1, Building 2B2, and Building 3C3
and REPLACE it with Building 1-A1, Building 2-B2, and Building 3-C3. This
could easily be performed if we could search for “Building ???†and replace
it with “Building ?-??â€, where the wildcards (?-??) in the replace string are
replaced by the wildcard characters found in the search string.
Unfortunately, using this example in the current version of Excel 2007, will
simply replace all the numbers with question marks instead of preserving the
wildcard characters in the find string.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...d-3e01e589bfed&dg=microsoft.public.excel.misc

 

[Stefan]

Why not allow matchingwildcards(? and *) in theREPLACEstring as well as
in the FIND string? Using the Excel FIND ANDREPLACEfeature is handicapped
by the inability to copy the wildcard strings from the FIND operation and
restore it as part of theREPLACEoperation. Often users want to use the
wildcard characters as a template to find strings in order toreplacethe
common component, but do NOT want toreplacethe wildcard component.

For example, we want to FIND Building 1A1, Building 2B2, and Building 3C3
andREPLACEit with Building 1-A1, Building 2-B2, and Building 3-C3. This
could easily be performed if we could search for "Building ???" andreplace
it with "Building ?-??", where thewildcards(?-??) in thereplacestring are
replaced by the wildcard characters found in the search string.
Unfortunately, using this example in the current version of Excel 2007, will
simplyreplaceall the numbers with question marks instead of preserving the
wildcard characters in the find string.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/community/en-us/default.mspx?mid=4aa6...

I am just having a similar problem....this is very annoying
indeed...and would be very easy to change I suppose...
So if I am not wrong it is not possible to replace for example

=ROUND(A1*B1;4)

by

= A1*B1

with the help of the replace function in excel....
I have a huge amount of sheets where I want to get rid of the round
function.....
would be great to find an automatic solution....
any suggestiongs?

thanks in advance!

Stefan

 

[Bernie Deitrick]

Stefan,

Manually, select the cells, then use Replace to:

Replace = with XXXX
Replace ;?) with nothing
Replace ROUND( with nothing
Replace XXXX with =

In a macro:

Sub RemoveROUNDFunction()
Selection.Replace What:="=", Replacement:="XXXX", LookAt:=xlPart
Selection.Replace What:="ROUND(", Replacement:="", LookAt:=xlPart
Selection.Replace What:=";?)", Replacement:="", LookAt:=xlPart
Selection.Replace What:="XXXX", Replacement:="=", LookAt:=xlPart
End Sub

HTH,
Bernie
MS Excel MVP

 

[Stefan]

Stefan,

Manually, select the cells, then use Replace to:

Replace = with XXXX
Replace ;?) with nothing
Replace ROUND( with nothing
Replace XXXX with =

In a macro:

Sub RemoveROUNDFunction()
Selection.Replace What:="=", Replacement:="XXXX", LookAt:=xlPart
Selection.Replace What:="ROUND(", Replacement:="", LookAt:=xlPart
Selection.Replace What:=";?)", Replacement:="", LookAt:=xlPart
Selection.Replace What:="XXXX", Replacement:="=", LookAt:=xlPart
End Sub

HTH,
Bernie
MS Excel MVP

Thanks a lot, that will save me quite some work

 

[The Blue Max]

There may be another simple method also, depending on what other formulas you
have included in the range. Try the following:

1. Select the range of cells to be changed.
2. Search for the function "Round(" and replace it with nothing.
3. Now Search for the trailing parameter ";4)" and replace it with nothing.

This process should leave you with only the cell references used as the
first argument.

Good Luck

***********************

 

[Stefan]

There may be another simple method also, depending on what other formulas you
have included in the range. Try the following:

1. Select the range of cells to be changed.
2. Search for the function "Round(" and replace it with nothing.
3. Now Search for the trailing parameter ";4)" and replace it with nothing.

This process should leave you with only the cell references used as the
first argument.

Good Luck

***********************

Hi again,

Well that is what I actually tried in the first place....the only
problem is that when replacing the "ROUND(" part Excel gives an error
message as the formula contains an error (the ";4)" that is still
left......so Excel asks me to change the formula what means I have to
do it by hand again.....
but the Macro proposed by Bernie worked fine.

Cheers,

Stefan