Read cell with comma as a number/value

Options

Dear all,

I have a column with numbers that can contain a comma. I need to do a sumif on this column, but Smartsheet reads this column as text, and therefore my formular does not work.

I have tried different ways to convert the column to a number in another column, but I have not managed to find a function that works.

  • I tried VALUE, but that removes my comma and turns 6,0 into 60.
  • I tried VALUE(LEFT but that rounds down turning 8,5 into 8,0.
  • I considered seperating my number into different columns, but the lenght of the numbers are not the same. It can be fx. 2 or 2,5 or 10,5. So using LEFT/MIDDLE/RIGHT in three different colums is difficult. (Hope this makes sense.)

I hope someone can help me.

Thanks in advance! :)

Best, Ida

Best Answer

Answers