Formula to Trim Cell From Right by Special Character
Hi,
I found this elsewhere on the forum and it works 100% as expected but as soon as I change out the special character to what I need it to be, I get Unparsable errors.
The formula that works is:
=RIGHT([Area Path]@row, FIND(">", [Area Path]@row, 1) - 1)
with the desired outcome:
But as soon as I change it to the character I actually need (not just following an example) it barfs:
=RIGHT([Area Path]@row, FIND("\", [Area Path]@row, 1) - 1)
So I know the function works, it's just not working for my character and I don't know why. Can somebody help me get this working to strip away everything up to and including the "\" please?
Best Answer
-
The FIND function outputs a number based on how far from the LEFT of a text string a character is found. The FIND function in the most recent screenshot is outputting 12. Dropping this into the RIGHT function means you are saying to pull the 12 rightmost characters.
Try subtracting the FIND output from a LEN function.
LEN([Area Path]@row) - FIND(…………)
Answers
-
I can't remember exactly what it is called, but the back-slash is a special kind of special character. I think it is called an "escape character" maybe.
Either way, try searching for a double back-slash instead.
FIND("\\", [Area Path]@row, 1)
-
thanks. that stopped it from barfing but gave unexpected results:
=RIGHT([Area Path]@row, FIND("\\", [Area Path]@row, 1) - 1)
The goal would be for the team name to just say World
-
The FIND function outputs a number based on how far from the LEFT of a text string a character is found. The FIND function in the most recent screenshot is outputting 12. Dropping this into the RIGHT function means you are saying to pull the 12 rightmost characters.
Try subtracting the FIND output from a LEN function.
LEN([Area Path]@row) - FIND(…………)
-
That worked, thanks
=RIGHT([Area Path]@row, LEN([Area Path]@row) - FIND("\\", [Area Path]@row, 1) - 0)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!