Google-sheets – Convert string to value within array formula

formulasgoogle sheets

I'm using the following formula to create an auto increment to form responses. I need the starting value of "100000" and all subsequent values to be a number, not a string.

Formula

=arrayformula( if( len(A2:A), text(row(A2:A) - row(A2) + 1, "100000"), iferror(1/0) ) )

How can I update the above formula so it outputs a number value, not a string showing a number?

Best Answer

Short answer

To numerate rows in a table, like the form responses, starting on 100000, use the following formula:

=FILTER(ROW(A2:A) - 2 + 100000,LEN(A2:A))

Explanation

  • ROW(A2:A) - 2 returns a array of consecutive numbers starting on 0.
  • + 100000 makes that the array of consecutive numbers start on 100000.
  • LEN(A2:A) as the filtering criteria argument of FILTER, makes that only the rows with responses be numerated.