16

In Google Sheets I have a column of values that are a combination of the letter S and a series of incrementing numbers.

How can I auto increment the sequence of numbers for each row instead of doing it manually?

For example:

S1
S2
S3
S4
. 
. 
. 
Sn
0

5 Answers 5

17

One way would be to provide the first two items, then mark both and drag at the lower right corner for as many rows as you like:

Dragging lower right corner

And the result is:

enter image description here

11

You can use a formula that concatenates S with the current row number:

="S"& Row()  

If your numbering does not start in Row 1, you can offset the row number in the formula.

For example, if you wish to start with S1 in Row 8, S2 in Row 9, and so forth, your adjusted formula would be:

="S"& row()-7

You could also use a single array formula to number multiple rows:

=ARRAYFORMULA("S"& row(A1:A20))

Offset Version:

=ARRAYFORMULA("S"& row(A8:A27)-7)
0
0

Automatically create a series or list

Source: Use autofill to complete a series (Google Docs Editors Help)

  1. On your computer, open a spreadsheet in Google Sheets.
  2. In a column or row, enter text, numbers, or dates in at least two cells next to each other.
  3. Highlight the cells. You’ll see a small blue box in the lower right corner.
  4. Drag the blue box any number of cells down or across.
    • If the cells form a series of dates or numbers, the series will continue across the selected cells.
    • If the cells don’t form a series of dates or numbers, the list of values will repeat across the selected cells.
0
0

Lets Say your column with values is "O" then the formula to generate ids can be:

=ARRAYFORMULA(IF(ISBLANK(O3:O) ,,"S"&ROW(O3:O)))
1
  • This answer does not appear to be to the question asked by OP. There is no requirement to skip rows.
    – Blindspots
    Feb 21 at 14:01
0

SEQUENCE

This function is used to create a sequence of numbers. For example if you want to create a sequence from 1 to 20:

=SEQUENCE(20)

You can prepend S onto it using ARRAYFORMULA

=ARRAYFORMULA("S" & SEQUENCE(20)) 

You can also create it dynamically from the current row up to the last row that contains a value.

For example, if your values are in Column B:

=ARRAYFORMULA("S"& 
   SEQUENCE(
     MAX(FILTER(
       ROW(B:B),LEN(B:B)))-ROW()+1))  

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.