Need formula to remove characters from a column when added

david.saunders

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.

Paul Newcome

Will it always be all text after the < or > that needs removed?

david.saunders

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.

Paul Newcome

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)