Data Search and Rounding

[bsheremeta]

I need to go through each cell, and if the data type is numeric, I need to round it to 1 decimal. Can someone suggest a formula I can use? I have like 20 columns and 50 rows with some nulls. 

Thank you!

[Luke_C]

Can you provide some sample data? Within a given column is there a mix of numeric and non-numeric data?

[bsheremeta]

@Luke_C 

[T_Willins · Accepted solution]

Hi @bsheremeta,

I would recommend data cleansing and formatting before rounding, but if you do not want to or cannot cleanse/transform your data, the attached workflow will round any numeric only "cell" to a single decimal.  The workflow uses two Multi-Field Formula tools.  The first Multi-Field Formula tool rounds all numeric fields to a single decimal.  The second Multi-Field Formula tool analyzes text fields for cells that are only numeric then rounds only those cells and leaves the other cells unchanged.  The RegEx Match formula (^\d+\.*\d*$) does the following:

^      From the start of the string

\d+   Find one or more numbers followed by

\.*    Zero or more periods followed by

\d*   Zero or more numbers

$    End of string

The ^ and $ in the formula discard any strings that have a combination of text and numeric.  

Let me know if you have questions.

[apathetichell]

I'd transpose and use regex to identify which columns are numbers and then round them as numbers and convert back to strings. Because your final columns are strings as set up - this is what jumped out to me...

I have a slightly different regex than @T_Willins  as I would go for: "\d+\.\d+" which is any number of numbers - one period and at least one digit... I hadn't really considered the starts with a period or negative number scenario which would require a [\$-]* as a leading entry before the first \d... I think for the second \d{2,} is probably more correct - since scenarios with one or fewer decimals won't require rounding.