Make Substitute replace all matches

[Denis_Peshekhonov]

I know there is RegexReplace formula. But why Substitute replaces only first match? I think there are more use cases with the need to replace all occurences, not only first.

[Mohamed_Hamad]

just came across this issue where i want to turn a string in a url parameter. needed to replace all spaces with dashes and lowercase. but found that substitute only replaces the 1st space, and not all matches

[Joel_Baerg]

Has anyone found a solution for this?

[Thomas_Schulz]

Would be interested as well, I have to replace several kind of text items, which can’t be solved with substitute, check here

substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(Substitute(thisRow.sy_title,"" ,"" ),"",""),"’",""),"?",""),":",""),",","")," –",""),“AI”,“AI”),"&amp",""),";",""),"&quot",""),")",""),"(","")

[Thomas_Schulz]

For sure our champion @Paul_Danyliuk already solved this earlier

[Federico_Stefanato]

What about a RegexReplace() chain?

[YourString].RegexReplace("find", "replace").RegexReplace("find another", "replace another")...
And so on.

Let me know if it helps.
Cheers!

[Thomas_Schulz]

Thanks @Federico_Stefanato will try right now – will be back

Update
thisRow.sy_title.RegexReplace("","" ).RegexReplace("","" ).RegexReplace("&#39","" ).RegexReplace(";","" ).RegexReplace("&amp","" ).RegexReplace("&quot","" ).RegexReplace(";","" )

And working

[Thomas_Schulz]

I hoped to get all the values in one formula w/o a chain, but anyway, let’s try

[Federico_Stefanato]

@Thomas_Schulz

I hoped to get all the values in one formula w/o a chain, but anyway, let’s try

Sure, in RegexpReplace() you can put several patterns in the find String separated by |
In your case:
[YourString].RegexpReplace("\"|;|&|,","")

Just be aware to escape special characters (e.g. quotes or slashes) prepending the \ character.

(Regular expressions - JavaScript | MDN)

Enjoy!

[Paul_Danyliuk]

Who summoned me from my slumber?

I myself use RegexReplace because I know regex and among all other hacky methods this one is the cleanest and most performant one. But please be wary that RegexReplace is NOT the same as Substitute that could replace all occurrences of a string. Sure, if you’re only looking to replace some safe alphanumeric characters then fine. But many characters in regex are reserved for special treatment.

E.g. you want to replace all periods in the string “A.01.02.03” with dashes. RegexReplace(".", "-") will NOT work as you expect but replace everything with a dash — that’s because . has a special meaning in regex: “match any character”.

Without regex, I guess the most (relatively) logical way would be to .Split().Join(), e.g.

Split("abracadabra", "a").Join("e")
// should result in ebrecedebre

Not the best approach because it takes some memory to first split strings into arrays and then join those back, but the shortest idiom, I guess.

P.S. I support @Denis_Peshekhonov’s original request that Subsitute should take the 2nd optional boolean parameter that would control whether to replace all or just one.

[Federico_Stefanato]

@Paul_Danyliuk

E.g. you want to replace all periods in the string “A.01.02.03” with dashes. RegexReplace(".", "-") will NOT work as you expect but replace everything with a dash — that’s because . has a special meaning in regex: “match any character”.

Sure, and that’s why it’s a regular expression - and not a string - replace.
In the given example you just need to escape the dot character to be considered as an actual dot.
i.e. [StringWithDots].RegexReplace("\.", "-").

As @Paul_Danyliuk suggests, double check with an online regexp reference.
Said that, for a full - and multiple - replacement, it is surely the most efficient way to achieve it.

[Nazzareno_Giannelli]

Thanks a lot for this Split+Join tip and the regex epxlanation!
I was going mad with those dots I wasn’t able to eradicate with a formula

[Eric_Koleda]

Hey everyone - I wanted to give you a heads up that we just added a new formula, SubstituteAll, that you can use to find and replace all instances of the search string.

SubstituteAll("banana", "a", "o") = "bonono"

We looked into changing the default behavior of Substitute, but making backwards-incompatible changes to the formula language is quite difficult. Hopefully this will make it easier going forward!

[joost_mineur]

Hey Eric,
Great addition to the formulas. Another nice parameter would be an “ignore case” option, where caps are replaced with caps and lower case with lower case. Saves us from using two formulas in a lot of situations. Work around like with lower() might lead to undesired results. I guess this wish alse pertains to substitute() - and it would be backwards compatible.
Greetings, Joost