Rounding Numbers with text column

[anitamarie2020]

Hey Guys, 

I have a column data set with both Numbers and Text, for some reason it's bringing in the text as '1.0, 2.0 etc' whereas I need it as 1 , 2 , 3 etc.

I tried to add the below formula but because I have text and numbers it's giving me the below

Does anyone had any idea how to fix?

This is my data set - the column I need it 'Basis'

Thanks,

Anita

[mbarone]

Change it right in the select tool instead of a formula.  Just change the data type to Int16, Int32, or Int64, depending on the size (I always do Int64 to be safe).

If that doesn't fix it, and you do have to use a formula tool, just wrap the field in ToNumber() first.

[anitamarie2020]

Thanks so much for getting back to me - for some reason when I change to INT16 it changes the letters in the column to 0

[mbarone]

In that case, you'll have no choice but the formula tool.  You'll need to create a new column since you can't update the current column's type with a formula tool.  So create a new column (maybe rename Basis before the formula tool with a select tool to something like "BasisX" and then create a new column with the formula tool called "Basis", set the type to Int64, and just use "ToNumber([BasisX])".  That should work!

[danilang · Accepted solution]

Hi @anitamarie2020 

If you have text and numbers in the same field you can use a formula like this

if tonumber([Field1]) =0 then
	[Field1]
else
	tostring(round(tonumber([field1]),1))
endif

If the field starts with text, to number will return 0 and just use the value of the field.  If not, the else clause converts it to a number, rounds it and converts the result back to a string.

This will only work if each row contains either a number or text.  If some rows contain a mix, such as "ABC 1.0 DEF"  you'll need a more complex solution, probably involving regex.  If this is the case post some sample data here.

Dan  

[anitamarie2020]

Thank you so much both that solved it