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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!