Is there a way to short this substitute formula?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 20 May 2008 19:35 GMT

I want to remove all periods, hyphens and comma's. The below works but is
there a better solution?

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K35,".","")," ",""),"-","")

Signature

Wag more, bark less

Reply to this Message

T. Valko - 20 May 2008 19:58 GMT

>is there a better solution?

From a formula, not really. The old_text argument can't be an array. That's
why you have to nest a new level for every different old_text argument.

Signature

Biff
Microsoft Excel MVP

>I want to remove all periods, hyphens and comma's. The below works but is
> there a better solution?
>
> =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K35,".","")," ",""),"-","")

Reply to this Message

Brad - 20 May 2008 20:19 GMT

Thanks.

Signature

Wag more, bark less

> >is there a better solution?
>
[quoted text clipped - 5 lines]
> >
> > =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K35,".","")," ",""),"-","")

Reply to this Message

T. Valko - 20 May 2008 21:39 GMT

You're welcome!

Signature

Biff
Microsoft Excel MVP

> Thanks.
>
[quoted text clipped - 9 lines]
>> >
>> > =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K35,".","")," ",""),"-","")

Reply to this Message