2

In an expression of the form (one column in, two out):

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

How can I convert just one of the output columns from text to a number (like VALUE)?

The documentation says:

If a number is desired as the output, try using the VALUE function in conjunction with this function.

However, if I do this:

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

I receive the error ("1" → 1 in this example):

Function REGEXREPLACE parameter 3 expects text values. But '1' is a number and cannot be coerced to a text.

How can I convert just one of the output columns to a number?

I have a complicated expression that produces output like this, and I need to SORT() on one of the numeric output columns, but I need to convert that column to a number or find some other way to properly sort it, because it sorts incorrectly as text (e.g. "1", "10", "2"), so if that's not possible, how can I SORT in numerical order on a text column in an array?

Improve this question
0

1 Answer 1

Reset to default
2

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.

  1. 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"})))
Improve this answer
1
  • #2 worked best for me here, never thought to split them. Expression is so cumbersome that I'm generating the expression itself with a shell script anyways, so no big deal to make it even more complex. Great ideas. Thanks.
    – Jason C
    Commented Jul 18, 2015 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.