If you need to remove illegal characters and special characters
There are characters that cannot or should not be used in file names, folder names, and query strings. (Explanatory articles on external sites areplease use this form.）
File names and query strings typically cannot contain illegal or special characters.
file name
For example, if you concatenate the value of 'Job ID' and 'Job Name' to create a field to use for the associated file name, then 'Job Name' contains characters that are not allowed in filenames (' ” / \ #, etc.) If it contains , you need to remove it or change it to a non-problematic character.
query string
In addition, "query string" in the form("query string", "URL parameter") to pre-populate the default value, "?, =, blank", etc. must be converted to a problem-free string. (e.g. blanks to %20)
SUBSTITUTE function
To replace an existing string in a string with a new string,SUBSTITUTE functionIs used.
For example, to change "," to "_" in the file name example above, the expression using the SUBSTITUTE function is as follows.
=[Job ID]@row + ” : ” + (SUBSTITUTE([Job Name]@row, “,”, “_”))
Also, in the case of the above query string, if you want to convert spaces, the expression is as follows.
=SUBSTITUTE([Some Value]@row, ” “, “%20”)
In the example below, if the query details @row contains '?', '=', and '&', processing is performed for URL percent encoding.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(query details@row, “?”, “%3F”), “=”, “%3D”), “&”, “%26”)
Handling of "Double Quotation Mark"
As you can see in the example of the expression above, the "double quotes" are used to specify the character string to be changed, so the "double quotes" themselves should be replaced with """"". If you specify it as a character string to be changed in the form, an error will occur.
OK: ”,” Not OK: “””
Method using CHAR function
To work around this problem, specify "double quotes" for the characters you want to change.CHAR functionIs used.
Use CHAR(34) instead of double quotes because the number corresponding to double quotes is 34.
demo sheet(CHAR(34))
The demo sheet below is an example of its use.
By reference to sheet summary values
Sheet summary values can be referenced in formulas by prefixing the field name with a #.
(The Smartsheet help article for the sheet summary isplease use this form., the explanation by Cloudsmart isplease use this form.）
For example, if you create a field called DQ in the sheet summary and put ""| as its value, you can refer to that value with "DB#" in the row formula.
Demo sheet (reference method for sheet summary values)
For example, in the demo sheet below, the formula below references the sheet summary value and removes the double quotes.
=SUBSTITUTE(Text@row, DQ#, “”)
