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