Concatenate incrementing row number with string in Google Sheets Arrayformula

1
votes

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?

2
arraysgoogle-sheetsconcatenationgoogle-sheets-formulaarray-formulas

2 Answers

1
votes

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})
0
votes

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?