nested substitute

ABHI · 07/05/19

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å · 07/06/19

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 · 07/08/19

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 · 07/08/19

Thank you Guys.

Andrée Starå · 07/08/19

Happy to help!

Let us know how it goes!

Paul Newcome · 07/08/19

Happy to help!

Comments