how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris

Do you really need a formula? Try using replace (Ctrl+H)--in Find what type
a comma (,) and in Replace with type an underscore (_).

"BorisS" wrote:

how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris

Works fine for me unless of course you have a number formatted like
currency

--

Regards,

Peo Sjoblom


"BorisS" wrote in message
...
how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris

On Thu, 29 Dec 2005 10:02:02 -0800, BorisS
wrote:

how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.

Your formula works fine for me.

You are aware, of course, that SUBSTITUTE is a text function and that
'reference' must refer to a text string? (see HELP for the function).

What is the nature of 'reference'?
--ron

Try this. I think your trying to substitute a comma in a number which needs
an extra step. This works for text and numbers

=SUBSTITUTE(TEXT(A1,"0,000"), ",","_")

"BorisS" wrote:

how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris

Actually, this is a text string, which is being used as an INDIRECT reference
for an INDEX to go and find a named range. Specifically:

Named range which it should be finding is MCB_1BR_MCC_JohnSmith2004Dollar.
This is a created name from what was a concatenated field of the following:

cell A19 which had "MCB (1BR, MCC)
cell B19 which had "John Smith"
cell D19 which had 2004
the word 'Dollar'

The formula for the cell which was used to name the range was
SUBSTITUTE(A19," ","")&SUBSTITUTE(B19," ","")&D19&"Dollars"

So I got rid of the spaces when making the name from which to create the
cell, and I guess Excel on its own changes parentheses and commas to
underscores when naming.

The place that looks for this named range is the following formula:

INDEX(INDIRECT(substitute(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3," ","")&D$2&"Dollars"),,2)

in which A3 was supposed to be "MC B (1BR, MCC)"

So I intended for the formula to pretend the parentheses were underscores,
as well as the commas, and just delete the spaces. The problem is it seems
to give me an error on trying to type this. The odd thing is that this Index
is the exact same - in terms of what's written - as another one, which works.
This one is part of a much larger formula:

IF(ActualEnd<12,SUM(SUM(INDEX(INDIRECT(substitute( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3,"
","")&D$2&"Dollars"),,2):INDEX(INDIRECT(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,ActualEnd+1)),SUM(INDEX(INDI RECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&C$2&"Dollars"),,ActualEnd+2):INDEX(INDIRECT( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,13))),SUM(INDIRECT(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),-INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A 3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3," ","")&D$2&"Dollars"),,1)))

The above is supposed to say that if the month which is indicated as actuals
ending is less than December, the formula is supposed to come up with a
calculated total which is equal to the months preceding the actual month in
the array named after the current year (current year being in D2) and take
all the month values that are in the post-ActualEnd part of the previous year
(previous year is in C2).

The index part

INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SU BSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B 3," ","")&C$2&"Dollars"),,14)

is the same as the entire contents of the previous cell, which calculates
the entire previous year. So I am not sure why that same index, as part of
the larger formula, is giving me errors.

Hope that makes sense.
--
Boris


"Sloth" wrote:

Try this. I think your trying to substitute a comma in a number which needs
an extra step. This works for text and numbers

=SUBSTITUTE(TEXT(A1,"0,000"), ",","_")

"BorisS" wrote:

how do I write in a substitution of a comma? SUBSTITUTE does not seem to
like it when I just type SUBSTITUTE(reference, ",","_"). I need it
substituted with an underscore.

Thanks.
--
Boris

On Thu, 29 Dec 2005 23:19:01 -0800, BorisS
wrote:

Actually, this is a text string, which is being used as an INDIRECT reference
for an INDEX to go and find a named range. Specifically:

Named range which it should be finding is MCB_1BR_MCC_JohnSmith2004Dollar.
This is a created name from what was a concatenated field of the following:

cell A19 which had "MCB (1BR, MCC)
cell B19 which had "John Smith"
cell D19 which had 2004
the word 'Dollar'

The formula for the cell which was used to name the range was
SUBSTITUTE(A19," ","")&SUBSTITUTE(B19," ","")&D19&"Dollars"

So I got rid of the spaces when making the name from which to create the
cell, and I guess Excel on its own changes parentheses and commas to
underscores when naming.

The place that looks for this named range is the following formula:

INDEX(INDIRECT(substitute(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B3 ," ","")&D$2&"Dollars"),,2)

in which A3 was supposed to be "MC B (1BR, MCC)"

So I intended for the formula to pretend the parentheses were underscores,
as well as the commas, and just delete the spaces. The problem is it seems
to give me an error on trying to type this. The odd thing is that this Index
is the exact same - in terms of what's written - as another one, which works.
This one is part of a much larger formula:

IF(ActualEnd<12,SUM(SUM(INDEX(INDIRECT(substitute (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B3 ,"
","")&D$2&"Dollars"),,2):INDEX(INDIRECT(SUBSTITUT E(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,ActualEnd+1)),SUM(INDEX(IND IRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&C$2&"Dollars"),,ActualEnd+2):INDEX(INDIRECT (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),,13))),SUM(INDIRECT(SUBSTITU TE(SUBSTITUTE(SUBSTITUTE($A3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3,"
","")&D$2&"Dollars"),-INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A 3,"
",""),"(","_"),")","_")&SUBSTITUTE($B3," ","")&D$2&"Dollars"),,1)))

The above is supposed to say that if the month which is indicated as actuals
ending is less than December, the formula is supposed to come up with a
calculated total which is equal to the months preceding the actual month in
the array named after the current year (current year being in D2) and take
all the month values that are in the post-ActualEnd part of the previous year
(previous year is in C2).

The index part

INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE($A3,"
",""),"(","_"),")","_"),",","_")&SUBSTITUTE($B3 ," ","")&C$2&"Dollars"),,14)

is the same as the entire contents of the previous cell, which calculates
the entire previous year. So I am not sure why that same index, as part of
the larger formula, is giving me errors.

Hope that makes sense.

I'm not sure exactly what you are doing, but one obvious problem is you are
running into the seven function nesting limit which is a specification of
Excel.

Some possible solutions:

1. Put the result of your "name construction" function in an intermediate
cell, and refer to that cell with your INDEX function.

2. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

The REGEX.SUBSTITUTE function can make multiple substitutions with a single
call, so would simplify your formula and reduce the nesting levels.

For example:

=REGEX.SUBSTITUTE(A19,"(\s)|(,)|(\()|(\))","[1=_,4=_]")

replaces <space with <underscore
<comma with nothing
<open parenthesis with nothing
<close parenthesis with underscore
all in one call.

So you could then use:

=REGEX.SUBSTITUTE(A19,"(\s)|(,)|(\()|(\))","[1=_,4=_]")&
SUBSTITUTE(B19," ","")&D19&"Dollar"

to construct your string

MCB_1BR_MCC_JohnSmith2004Dollar

and eliminate a lot of the nesting levels.

--ron