Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion

    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.

  • Community Champion

    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))

  • Community Champion

    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?

  • Community Champion

    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?

  • Community Champion

    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?

  • Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions