Remove Symbols

MatR

OK, so, we have an update form that goes out to about 30 people. All they have to do is add the latest figures and click 'Send'.

That's it.

However, they insist on adding in symbols and text, no matter how strenuously we explain that they shouldn't. All they need to do is enter 55 not £55.00 or 0 not N/A. But they persist. We've even taken to renaming an entire column with the suffix '(do not use symbols: £, %, N/A etc)' but it does no good.

This means that we have to perform data cleansing on the Smartsheet before we export it, and I'd like to create a simple Alert to highlight those rows that need fixing.

Now, finding the 'N/As' and other text is straightforward, but how do I find the currency or percentage cells? As soon as a currency or percentage symbol is added it changes the cell to that format, but the symbol (£ or %) becomes part of the formatting of the cell, so formulas don't recognise it as something to be removed.

Any ideas?

baddams

Creating a column with the value() formula will remove the currencies from the number. You could even do an if, then statement to say if N/a, then 0, otherwise value(cell)

Mike Wilday

I agree with @baddams. I would create a new column and use the value formula. To enter 0's for blank cells you can do

=IF(OR([Column with symbols Name]@row = "", Column with Symbols name@row = "N/A"), 0, Value(Column with symbols name@row))

The Above Formula should catch all N/A's or blanks and assign 0 value.