| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why not accept wildcards for REPLACE as well as FIND ?
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/comm...lic.excel.misc |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why not accept wildcards for REPLACE as well as FIND ?
On Jul 12, 5:20 am, The Blue Max
wrote: 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/comm....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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why not accept wildcards for REPLACE as well as FIND ?
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" wrote in message ups.com... On Jul 12, 5:20 am, The Blue Max wrote: 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/comm....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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why not accept wildcards for REPLACE as well as FIND ?
On Jul 25, 2:44 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: 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" wrote in message ups.com... On Jul 12, 5:20 am, The Blue Max wrote: 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/comm....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 Thanks a lot, that will save me quite some work :) |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why not accept wildcards for REPLACE as well as FIND ?
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" wrote: On Jul 12, 5:20 am, The Blue Max wrote: 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/comm....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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Why not accept wildcards for REPLACE as well as FIND ?
On Jul 25, 6:32 pm, The Blue Max
wrote: 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" wrote: On Jul 12, 5:20 am, The Blue Max wrote: 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/comm....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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Replace using Wildcards | Excel Worksheet Functions | |||
| sumproduct won't accept wildcards | Excel Worksheet Functions | |||
| use replace to edit a formula with wildcards | Excel Worksheet Functions | |||
| Replace using wildcards | Excel Discussion (Misc queries) | |||
| How can I find strings of wildcards in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
