ExcelBanter - Removing Quotes from Range Names

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Removing Quotes from Range Names (https://www.excelbanter.com/excel-programming/430019-removing-quotes-range-names.html)

goody

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

Jacob Skaria

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

Bob Phillips[_3_]

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

goody

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

Bob Phillips[_3_]

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

goody

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

Bob Phillips[_3_]

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

goody

Removing Quotes from Range Names

All times are GMT +1. The time now is 06:58 AM.