Google-sheets – Google sheets – display only rows containing a digit in range [1-9]

Part of my answer would be: this is a known bug (newly received form submissions do not respect the format of the columns that they are landing in, as they did in the old version of sheets).

Depending on what you are doing with the data afterwards, another option would be to reproduce the form submissions on another sheet, with the correct format. For example, if column E contained zip codes that should be 5 digits long and include leading zeros (if required), then you could use:

=ArrayFormula(TEXT('Form Responses'!E:E,"00000"))

on another sheet.

You would need to bring over all your other columns as well, though. Without getting too complicated, this can be reasonably easily achieved in the new version of Sheets, and format the column(s) as desired, with (eg):

=QUERY('Form Responses'!A:G,"select * format E '00000'",1)

Depending on the details of your use case, one of the following may be preferable:

1. Add a column with =ARRAYFORMULA(VALUE( B1:B100 )), assuming B1:B100 is the first output column. Then sort by this added column. This may be the easiest way to achieve the sort that you want.

2. Apply Value only to the first output. To my understanding, this requires splitting the regexreplace, so that first the replacement is made, then Value is applied, and only then the embedded array {...} is formed:

=ARRAYFORMULA({VALUE(REGEXREPLACE(A1:A100,...,"$1")), REGEXREPLACE(A1:A100,...,"$2")} )

If you choose this path, you might as well have two different formulas in two columns, one =ARRAYFORMULA(VALUE(REGEXREPLACE(A1:A100,...,"$1"))) and the other =ARRAYFORMULA(REGEXREPLACE(A1:A100,...,"$2")). I think this version is the most readable and flexible one.

3. Apply Value whenever it doesn't throw an error (regardless of columns). This could be done with IfError:

=ARRAYFORMULA(IFERROR(VALUE(REGEXREPLACE(A1:A100,...,{"$1", "$2"})),REGEXREPLACE(A1:A100,...,{"$1", "$2"})))