Concatenate incrementing row number with string in Google Sheets Arrayformula

Question

I'm trying to get a column to automatically fill up with incrementing strings depending on the row number in an ARRAYFORMULA in Google sheets. I can easily get a column to show a shifted row number by putting =ARRAYFORMULA(ROW(A:A)-1) in the top cell. However trying to use this ROW call inside CONCATENATE does not give me the result I want, I've tried with and without TO_TEXT.

In the image the formula used for the different columns are:

A1: =ARRAYFORMULA(ROW(A:A)-1)

B1: =ARRAYFORMULA(IF(ROW(B1:B)>1,CONCATENATE("Hello World: ",(ROW()-1)),"Hello there world!"))

C1: =ARRAYFORMULA(IF(ROW(C1:C)>1,CONCATENATE("Hello World: ",TO_TEXT(ROW(C:C)-1)),"Hello there world!"))

D1:D16: Manually entered the desired strings.

Spreadsheet screen shot

As you can see, B1 doesn't increment the number, only uses the ROW value from the cell where the formula is given. And C1 concatenates all the numbers of the rows into every line.

Any ideas on how I can accomplish the intended result without filling or using scripts?

player0 · Accepted Answer · 2019-11-05 18:00:44Z

1

there is an unwritten rule that says to never use CONCATENATE unless you really need it which is also always never. to join stuff all you need is &

=ARRAYFORMULA({"Hello there world!"; "Hello World: "&ROW(A2:A)-1})

answered Nov 5, 2019 at 18:00

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JPV · Accepted Answer · 2019-11-05 17:01:34Z

0

In B1 try

=ARRAYFORMULA(IF(ROW(B:B)>1, "Hello World: "&ROW(B:B)-1,"Hello there world!"))

or

={"Hello there world!"; ARRAYFORMULA("Hello World: "&ROW(B2:B)-1)}

and see if that works?

edited Nov 5, 2019 at 17:01

answered Nov 5, 2019 at 16:56

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Collectives™ on Stack Overflow

Concatenate incrementing row number with string in Google Sheets Arrayformula

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related