TIP: How to reference a \ backslash or " double quotes in formulas + [ ] in Col Names

While researching a problem I had and the absolutely simple solution that was causing it, I decided I would write this post to help people in the future. This is not an official post, just me trying to give back to the community in hopes it helps someone, so if you have more info to add (or correct me) please post it below!
Did you know you cannot reference a \ (Backslash) or ” (Double Quotes) like you would all the other symbols on the keyboard? Yeah, that's why you're here! ;)
For example, using the SUBSTITUTE Function to replace a backsplash with another character, you’ll get an #UNPARSEABLE error without adding something called an “Escape Character” in your formula.
An escape character in Smartsheet is just a simple backslash \ and is placed in front of a special character to indicate that the following character should be treated differently.
This Formula Doesn’t work
: SUBSTITUTE(Text@row, "\", "[backslash]"
This Formula Works:
SUBSTITUTE(Text@row, "\\", "[backslash]"
SUBSTITUTE isn’t the only function that needs it, it’s true for ALL formulas using the following symbols:
Symbols That Require Escape Characters:
- “ (Double Quotes)
- \ (Backslash)
Escape Characters for [ (left bracket) and ] (right bracket) also need the \ escape character added in front of them ONLY IF the bracket being referenced is part of the column name. (as far as I know)
- ] -- ONLY if it’s in the COLUMN HEADER
- ] -- ONLY if it’s in the COLUMN HEADER
The \ backslash must include an Escape Character in BOTH situations—In formulas and when referencing Column Names containing a backslash \.
It’s also important to remember to add the \ before importing data or you could end up with unwanted symbols in your data.
None of us want to admit how much time finding this simple solution cost us 😊. But at least now you can remember to add the escape character \ to avoid these very frustrating #UNPARSEABLE errors in the future.
TLDR; When writing formulas containing a \ or “, use \\ or \” instead. When referencing column names with \ or [ or ], use \\ or \[ or \] instead.
-Neil
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.4K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 492 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives