Replace or Substitute functions

[Doug]

I have a field the may contain spaces, dashes and slashes for instance
4022916389 /5-12-03 trans. I want to create a formula that will
replace all of these characters with "no space".

The formula =SUBSTITUTE(A1,"-","") will only remove dashes.

Is there a way to nest multiple replace or substitute functions
together to accomplish my request? If so, please let me know.

Thanks for your help

 

[Peo Sjoblom]

Use 2

=SUBSTITUTE(SUBSTITUTE(A1,"-",""),"/","")

 

[Tushar Mehta]

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm
you can then use
=RegExpSubstitute(A13,"[ /-]","")
where A13 contains your original string. Depending on what other
characters you want to remove, you might also want to consider
=RegExpSubstitute(A13,"(\W|_)","")

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Mike A]

Tushar,

These are awesome functions! I made an add-in so that I always have
them! Regular expressions are one thing I always miss when I am not
working on UNIX!

Thanks!

-Mike

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm
you can then use
=RegExpSubstitute(A13,"[ /-]","")
where A13 contains your original string. Depending on what other
characters you want to remove, you might also want to consider
=RegExpSubstitute(A13,"(\W|_)","")

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Tushar Mehta]

Thanks. Yes, regular expressions can be very useful.
--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Tushar,

These are awesome functions! I made an add-in so that I always have
them! Regular expressions are one thing I always miss when I am not
working on UNIX!

Thanks!

-Mike

If you install the functions at
http://www.tmehta.com/regexp/add_code.htm
you can then use
=RegExpSubstitute(A13,"[ /-]","")
where A13 contains your original string. Depending on what other
characters you want to remove, you might also want to consider
=RegExpSubstitute(A13,"(\W|_)","")

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers