Removing Illegal Characters using nested SUBSTITUTE failing
Hello, I am trying to use the following formula to remove illegal characters from a string (situated in column "name"), but it fails as unparsable. Can anyone tell me why? Is there a limit I've gone over?
I note that the formula is trying to use the brackets (parentheses in USA) even though they are enclosed in quotation marks. However, even if I reduce the formula by 2 nests to get rid of these from the formula, it still fails.
The formula used to work in removing spaces and full stops (periods), but in this "enhanced" version, it doesn't work any more.
Any suggestions?
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(name2, " ", ""), "'", ""), "`", ""), "(", ""), ")", ""), "[", ""), "]", ""), "-", ""), ".", ""), ",", ""), ":", ""), "&", ""), """, ""), "*", ""), "/", "")
Best Answer
-
That's a great question. No, it's not impossible to remove a quotation mark using a formula, however we just can't reference it as the symbol itself. Instead we can use the ASCII code to reference the " symbol.
CHAR(34) is the code for "
Try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(name@row, " ", ""), "'", ""), "`", ""), "(", ""), ")", ""), "[", ""), "]", ""), "-", ""), ".", ""), ",", ""), ":", ""), "&", ""), CHAR(34), ""), "*", ""), "/", "")
Let me know if this works for you!
Cheers,
Genevieve
Answers
-
The statement which is causing the #UNPARSEABLE error is actually just the one statement that is trying to find a quotation mark: "
Your third statement from the end says this:
""", "")
The three quotes in a row is what's causing the formula to error, it's reading the quote as part of the formula instead of something to search for.
Depending on how the initial values are input, you may want to search for a different type of character quote. For example, quotes typed into Smartsheet (and used in formulas) are straight lines like so: "
However quotes typed in other places can sometimes create different characters, which show up curved, like so: “
You can search for this second type of quote because the formula won't recognize it as a formula quote:
"“", "")
Do you see how it's angled a bit differently between the two formula quotes?
Otherwise, you may want to remove this one section from your formula and the rest of the SUBSTITUTES should work without an error.
Cheers,
Genevieve
-
Thank you for the answer, Genevieve. I'll have a look at this a bit later. Are you saying that it is impossible to remove this type of quotation mark from a string?
What our setup is, is that a user adds information to a Smartform (ignoring the rules that say no punctuation marks should be included). The data arrives in the Smartsheet and needs to have any punctuation marks and spaces removed, then that is used to create a csv for import into another application.
Any quotation marks or other punctuation marks will cause that import to fail - so is it possible to remove these another way? perhaps a different way of referring to them?
-
That's a great question. No, it's not impossible to remove a quotation mark using a formula, however we just can't reference it as the symbol itself. Instead we can use the ASCII code to reference the " symbol.
CHAR(34) is the code for "
Try this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(name@row, " ", ""), "'", ""), "`", ""), "(", ""), ")", ""), "[", ""), "]", ""), "-", ""), ".", ""), ",", ""), ":", ""), "&", ""), CHAR(34), ""), "*", ""), "/", "")
Let me know if this works for you!
Cheers,
Genevieve
-
Thank you. That's perfect. Appreciate the help.
-
No problem! I should have thought of the code right away. I'm glad it will work for you. 🙂
Help Article Resources
Categories
Check out the Formula Handbook template!
