Need formula to remove characters from a column when added
We export data from another data source and cut and paste it in the Smartsheets Column AOY Status.
The issue is after we cut and paste the data there are going to be certain characters I need removed.
For example: The data we cut and paste in will have Down <20 days or Down >20 days.
When we cut and paste that data I would like to set up a formula that will automatically remove the >20 days or <20 days
I am not sure who to do that in Smartsheets.
Please advise.
Answers
-
Will it always be all text after the < or > that needs removed?
-
Correct. The people using the form just cut and paste the data in the column and we are trying to elimintate them having to manually remove them. *This is going to be a very widely used report used by many that have little computer skills and we are needing to make it as basic and easy to use as possible.
I would like for them to be able to just cut and paste data in that column and have that <>20days removed for the formula in place to work.
-
The only way to get it removed from the cell they are pasting into is through the API, the premium add-on Bridge, or some other similar third party app.
But we can use a formula in a helper column to remove the piece of the string.
=IFERROR(IFERROR(LEFT([Column Name]@row, FIND("<", [Column Name]@row) - 1), LEFT([Column Name]@row, FIND(">", [Column Name]@row) - 1), [Column Name]@row)
-
I have a Premium membership if there are any tool in that. If you have any suggestions where that might work.
I am trying to input that formula and it is saying #INCORRECT ARGUMENT SET ?
What am I doing wrong? Did I input something wrong in the formula?
=IFERROR(IFERROR(LEFT([AOY Status]@row, FIND("<", [AOY Status]@row) - 1), LEFT([AOY Status]@row, FIND(">", [AOY Status]@row) - 1), [AOY Status]@row))
-
It may be a misplaced closing parenthesis. Try this:
=IFERROR(IFERROR(LEFT([AOY Status]@row, FIND("<", [AOY Status]@row) - 1), LEFT([AOY Status]@row, FIND(">", [AOY Status]@row) - 1)), [AOY Status]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.5K Get Help
- 448 Global Discussions
- 145 Industry Talk
- 481 Announcements
- 5.1K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 73 Community Job Board
- 492 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 304 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!