nested substitute

ABHI

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

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

Andrée Starå

Hi,

Try something like this.

=SUBSTITUTE(SUBSTITUTE(Text@row; "-"; ""); "."; "")

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

=SUBSTITUTE(SUBSTITUTE(Text@row, "-", ""), ".", "")

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

Paul Newcome

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(Text@row, "-", "")

.

To remove the "."

 

=SUBSTITUTE(Text@row, ".", "")

.

To nest them, just drop one of the SUBSTITUTE formulas into the Text@row portion of the other.

 

=SUBSTITUTE(SUBSTITUTE(Text@row, ".", ""), "-", "")

.

If you then wanted to remove a "?"

 

=SUBSTITUTE(Text@row, "?", "")

 

and then just drop that into the Text@row portion again just like the last one.

 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Text@row, "?", ""), ".", ""), "-", "")

 

ABHI

Thank you Guys.

Andrée Starå

Happy to help!

Let us know how it goes!

Paul Newcome

Happy to help!