| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Quotes from Range Names
I have a column of labels containing spaces, hyphens, parentheses, etc., that
Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
To remove double quotes use Chr(34) instead of """".
=Replace(strData,Chr(34),"_") Would suggest to use VBA Replace function If this post helps click Yes --------------- Jacob Skaria "Goody" wrote: I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
Do you have to have the replacement in the form, can we do it in code
Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
Thanks, Bob. Your solution works great. I now have a new problem. When Excel
encounters a right parenthesis at the end of the label, it does not convert it to an underscore when using the label as a range name. How can I duplicate that it code, while still converting parentheses within the label? Goody "Bob Phillips" wrote: Do you have to have the replacement in the form, can we do it in code Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
I am not sure what you mean. I created a simple value of (help) and the code
converted that to _help_ as required. What am I missing? -- __________________________________ HTH Bob "Goody" wrote in message ... Thanks, Bob. Your solution works great. I now have a new problem. When Excel encounters a right parenthesis at the end of the label, it does not convert it to an underscore when using the label as a range name. How can I duplicate that it code, while still converting parentheses within the label? Goody "Bob Phillips" wrote: Do you have to have the replacement in the form, can we do it in code Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
I use the formula that the code creates to sum cells in a large table. The
rows in the table were assigned names using the Insert, Name, Create command. When Excel creates range names using this method, it does not convert a right parenthesis at the end of the label to an underscore; but the code you helped me with does. I assume I could use an IF test in the code to check the position of the right parenthesis, and drop it if it is at the end of the label. I was wondering if there were an easier way. Goody "Bob Phillips" wrote: I am not sure what you mean. I created a simple value of (help) and the code converted that to _help_ as required. What am I missing? -- __________________________________ HTH Bob "Goody" wrote in message ... Thanks, Bob. Your solution works great. I now have a new problem. When Excel encounters a right parenthesis at the end of the label, it does not convert it to an underscore when using the label as a range name. How can I duplicate that it code, while still converting parentheses within the label? Goody "Bob Phillips" wrote: Do you have to have the replacement in the form, can we do it in code Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
I would do that, test the last char for a ) and lose it.
-- __________________________________ HTH Bob "Goody" wrote in message ... I use the formula that the code creates to sum cells in a large table. The rows in the table were assigned names using the Insert, Name, Create command. When Excel creates range names using this method, it does not convert a right parenthesis at the end of the label to an underscore; but the code you helped me with does. I assume I could use an IF test in the code to check the position of the right parenthesis, and drop it if it is at the end of the label. I was wondering if there were an easier way. Goody "Bob Phillips" wrote: I am not sure what you mean. I created a simple value of (help) and the code converted that to _help_ as required. What am I missing? -- __________________________________ HTH Bob "Goody" wrote in message ... Thanks, Bob. Your solution works great. I now have a new problem. When Excel encounters a right parenthesis at the end of the label, it does not convert it to an underscore when using the label as a range name. How can I duplicate that it code, while still converting parentheses within the label? Goody "Bob Phillips" wrote: Do you have to have the replacement in the form, can we do it in code Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Removing Quotes from Range Names
I use the formula that the code creates to sum cells in a large table. The
rows in the table were assigned names using the Insert, Name, Create command. When Excel creates range names using this method, it does not convert a right parenthesis at the end of the label to an underscore; but the code you helped me with does. I assume I could use an IF test in the code to check the position of the right parenthesis, and drop it if it is at the end of the label. I was wondering if there were an easier way. Goody "Bob Phillips" wrote: I am not sure what you mean. I created a simple value of (help) and the code converted that to _help_ as required. What am I missing? -- __________________________________ HTH Bob "Goody" wrote in message ... Thanks, Bob. Your solution works great. I now have a new problem. When Excel encounters a right parenthesis at the end of the label, it does not convert it to an underscore when using the label as a range name. How can I duplicate that it code, while still converting parentheses within the label? Goody "Bob Phillips" wrote: Do you have to have the replacement in the form, can we do it in code Worksheets("Material").Range("B4").FormulaArray = _ "=SUM(" & Replace(Replace(Replace(Replace(Replace(Replace(Re place(Range("A4").Value, " ", "_"), "(", "_"), ")", "_"), "/", "_"), "&", "_"), ",", "_"), "-", "_") & "*Unit_Cost)" -- __________________________________ HTH Bob "Goody" wrote in message ... I have a column of labels containing spaces, hyphens, parentheses, etc., that Excel automatically converts to underscores when I use the labels as range names. I am using a string of SUBSTITUTE commands ina a macro to build an array formula that coverts the labels to range names. The macro functions as intended, except the resulting range name is always in quotes (e.g., "Test_Number"). The array formula then returns #VALUE!, because it does not recognize the range. How can I remove the quotes? The macro line is shown below: Worksheets("Material").Range("AB4").FormulaArray = "=Sum(Substitute(Substitute(Substitute(Substitute( Substitute(Substitute(Substitute(R4C1,"" "",""_""),""("",""_""),"")"",""_""),""/"",""_""),""&"",""_""),"","",""_""),""-"",""_"") * Unit_Cost)" Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| in excel, how do i remove quotes from column of 700 names | Excel Discussion (Misc queries) | |||
| Using range names or ranges between quotes | Excel Programming | |||
| removing single quotes from a paste operation | Excel Programming | |||
| Removing range names. | Excel Discussion (Misc queries) | |||
| Removing Range Names | Excel Programming | |||
Linear Mode
