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)
-
It is saying #unparseable when i input that formula. i just made a new column and inputted that formula in it. Was i doing that correctly?
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
not sure if this helps… but if i was doing this in excel i would use the below formula:
=LEFT(AOY Status, SEARCH("<20 Days", AOY Status)-1)
but when i input that info it is saying #unparseable.
just trying to work through some solutions… thanks for your assistance on this Big Project.
Not sure if it helps… but the only time the <20 days or > 20 days will show up is after the word down. so maybe maybe a formula that just removes everything after the word down?
-
I'm not sure why it is throwing that error message. Try retyping it from scratch to make sure all quotes are as they should be and whatnot.
There is no SEARCH function in Smartsheet. That is the FIND function. Each LEFT function is operating exactly the same as what you would have in Excel.
-
I typed it in and it is now saying #incorrect Argument Set. I believe #incorrect argument set means we have too many arguments in a function?
still not working but got different error code on given formula. i typed it in item by item to match your formula given
=IFERROR(IFERROR(LEFT([AOY Status]@row), FIND("<", [AOY Status]@row - 1), LEFT([AOY Status]@row, FIND(">", [AOY Status]@row) - 1)), [AOY Status]@row)
If that formula has too many arguments :
*is there another formula we can put in to just remove everything after the word down when it appears if that is the issue?
-
That is coming from this part:
FIND("<", [AOY Status]@row - 1)
There is a missing parenthesis, so you are trying to subtract 1 from [AOY Status]@row instead of subtracting 1 from the output of the FIND function. You also closed off the LEFT function before establishing how many characters.
What does this give you?
=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
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!