Subsitute format

[Emily]

The format for SUBSTITUTE should be, say:
SUBSTITUTE(A1, "0", "")

Why SUBSTITUTE(A1, {"0", "1", "2") , "") be accepted?

The substitute only replace the first element of the set, i.e 0 only.

Thanks.

 

[Andrew Poulsom]

It seems you can't pass an array to SUBSTITUTE. Try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2","")

 

[Emily]

On 2002-10-17 05:17, Andrew Poulsom wrote:
It seems you can't pass an array to SUBSTITUTE. Try:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0",""),"1",""),"2","")

Thanks.

But this is not the point I am looking for.
I found my answer as the link shown below.

It's great, worth to read.

http://groups.google.co.nz/groups?h....7707523%40bgtnsc06-news.ops.worldnet.att.net

 

[Andrew Poulsom]

I think that was my point. You can't pass an array to SUBSTITUTE if used alone - it only evaluates the first aray element.

But SUBSTITUTE nested within a function which accepts an array (eg SUMPRODUCT) returns an array to that function. I wasn't aware of that, so thanks for the link.

 

[Emily]

But SUBSTITUTE nested within a function which accepts an array (eg SUMPRODUCT) returns an array to that function. I wasn't aware of that, so thanks for the link.

Andrew
Would you please give me an example.
Thanks

 

[Andrew Poulsom]

There's one in your link:

=SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))),"")

The second SUBSTITUTE passes an array to SUMPRODUCT.

 

[Emily]

Oh, I missed that
Thanks

 

[Aladin Akyurek]

On 2002-10-18 05:48, Emily wrote:
Oh, I missed that
Thanks

FYI, I'm the author.

Aladin