1

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?

  • Yes. Then I use =ISNUMBER(E2) to test it and get "false". I need to figure out how to return the auto increment number as a 'value'. – Mr. B Sep 7 '16 at 2:37
  • Column is the time/date stamp from a Google Form submission. This formula is in an empty column (G) and all it does is place an auto increment number in the cells 'when' something appears in column A. – Mr. B Sep 7 '16 at 2:54
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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