Numbers in Form

Wojciech K

My Regional Number Settings are: 2 500,00.
In the Form, I have a field that only allows numbers. Form input rows to the worksheet.
Unfortunately, the Form doesn't allow the "," entry. In the Form, the number must be entered as 2500.00. However, such a value transfered to the column (with setting as Text/number) in worksheet cannot be compared (Conditions) in Workflow, probably because the value isn't treated as a number.
How can this be improved?

jmyzk_cloudsmart_jp

Hi @Wojciech K

If you want to convert a number input by a form to the formatted regional number, you can use this formula;

[Formatted Regional Number] =

IF(

  ISNUMBER([Regional Number]@row),

  IF(

    [Regional Number]@row >= 1000000,

    LEFT([Regional Number]@row, LEN([Regional Number]@row) - 6) + " " +

    MID([Regional Number]@row, LEN([Regional Number]@row) - 5, 3) + " " +

    MID([Regional Number]@row, LEN([Regional Number]@row) - 2, 3) + ",00",

    IF(

      [Regional Number]@row >= 1000,

      LEFT([Regional Number]@row, LEN([Regional Number]@row) - 3) + " " +

      RIGHT([Regional Number]@row, 3) + ",00",

      [Regional Number]@row + ",00"

    )

  )

)

or

[Formatted Regional Number] =IF(ISNUMBER([Regional Number]@row ), IF([Regional Number]@row >= 1000000, LEFT([Regional Number]@row , LEN([Regional Number]@row ) - 6) + " " + MID([Regional Number]@row , LEN([Regional Number]@row ) - 5, 3) + " " + MID([Regional Number]@row , LEN([Regional Number]@row ) - 2, 3) + ",00", IF([Regional Number]@row >= 1000, LEFT([Regional Number]@row , LEN([Regional Number]@row ) - 3) + " " + RIGHT([Regional Number]@row , 3) + ",00", [Regional Number]@row + ",00")))

https://app.smartsheet.com/b/publish?EQBCT=9b63e7a2ef9442f6aa3b48331be921df

If you want to convert a text input from a form to a number-converted regional number, you can use this formula;

[Number Converted RN Text] =
IF(ISTEXT([Regional Number Text]@row ), 
  VALUE(SUBSTITUTE(SUBSTITUTE([Regional Number Text]@row , " ", ""), ",", ".")))

or

[Number Converted RN Text] =IF(ISTEXT([Regional Number Text]@row ), VALUE(SUBSTITUTE(SUBSTITUTE([Regional Number Text]@row , " ", ""), ",", ".")))