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

danilang

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