Please add the SUBSTITUTE() function for calculated fields
The current calculated field functions don't allow for the simple removal or substitution of character strings within a larger text field, e.g. removal of embedded spaces in a target string. TRIM() works well for leading/trailing spaces, but there is currently NO simple, useable solution for embedded spaces, or the replacement of other characters where the position is not known in the string. The equivalent in JSON would be a regex.replace()
Please enable the EXCEL function SUBSTITUTE() for calculated fields, as SUBSTITUTE(<target field>,<replace target text>,<replace with text>)
e.g. SUBSTITUTE([Text String]," ","") would replace all occurrences of space in the field with a null value, effectively removing the embedded (or leading/trailing spaces altogether, so "This is my string" >> "Thisismystring"
SUBSTITUTE([Text String]," ",".") would replace all spaces in the string with ".", so "This is my string">> "This.is.my.string".
The replace target text should be able to be of any length as long as the string to be replaced is contained within the length of the text being manipulated, e.g.
SUBSTITUTE([Text String],"my',"your") = "This is my string">> "This is your string"
If the text to be replaced is not found in the target field, the result is the same as the target field, e.g.
SUBSTITUTE([Text String], "John", "Joe") where [Text String]= "This is Jane's string">> "This is Jane's string"
Please expedite this as it has been a gap in the calculated field functions for an awful long time now, and there is no real workaround for it.