Google-sheets – RegexReplace produces an error

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"})))  

This is probably best handled by a custom function. The function I wrote takes the data range (including the header row) as an argument and outputs the new table, with Ids and its header. Usage example: =inOut(A1:C20). It also checks data for consistency: the times must begin with In and then alternate between In and Out.

function inOut(arr) {
  var data = {};
  var maxLen = 0;
  for (var i = 1; i < arr.length; i++) {
    var id = arr[i][0];
    if (!data[id]) {
      data[id] = [];
    }
    var expectedType = (data[id].length % 2 ? 'Out' : 'In');
    if (arr[i][1] != expectedType) {
      throw new Error('Inconsistent data on line ' + i + ': expected ' + expectedType + ' and got ' + arr[i][1]);
    }
    data[id].push(arr[i][2]);
    if (data[id].length > maxLen) {
      maxLen = data[id].length;
    }
  }

  // End of data collection; proceed to form output

  var keys = Object.keys(data).sort();
  var headerRow = ['EmpId'];
  for (i = 0; i < maxLen; i++) {
    headerRow.push(i % 2 ? 'Out' : 'In');
  }
  var output = [headerRow];
  for (i = 0; i < keys.length; i++) {
    id = keys[i];
    var row = [id].concat(data[id]);
    while (row.length < maxLen + 1) {
      row.push('');
    }
    output.push(row);
  }
  return output;
}