5

How can I use substitute to replace not only one but multiple letters?

For eg:                          -0.-9                   I want to get 09 only.

Functionality

Comments

Andree_Stara

Hi,

Try something like this.

=SUBSTITUTE(SUBSTITUTE([email protected]; "-"; ""); "."; "")

The same version but with the below changes for your and others convenience.

=SUBSTITUTE(SUBSTITUTE([email protected], "-", ""), ".", "")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

I have found the easiest way to nest SUBSTITUTE functions is to build out from the middle instead of trying to work from left to right.

 

To remove the "-", you would use

 

=SUBSTITUTE([email protected], "-", "")

.

To remove the "."

 

=SUBSTITUTE([email protected], ".", "")

.

To nest them, just drop one of the SUBSTITUTE formulas into the [email protected] portion of the other.

 

=SUBSTITUTE(SUBSTITUTE([email protected], ".", ""), "-", "")

.

If you then wanted to remove a "?"

 

=SUBSTITUTE([email protected], "?", "")

 

and then just drop that into the [email protected] portion again just like the last one.

 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([email protected], "?", ""), ".", ""), "-", "")

 

Thank you Guys.