Google Sheets – How to CONCATENATE a String with an ArrayFormula

concatenategoogle sheets

I have the following string in column H and I.

H2="http://"
I2="aaa"
H3="http://"
I3="bbb"

I want to have a auto-generated J column, which results like:

J2="http://aaa"
J3="http://bbb"

So I put the following formula in J1:

= ARRAYFORMULA (IF(ROW(J:J)=1;"formulaTest";CONCATENATE(H:H1, I:I1 ) ))

But the result I got is:

J2="http://http://aaabbb"
J3="http://http://aaabbb"

What is going wrong with my formula?

Best Answer

This is what you are looking for:

=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))

Related Solutions

Google Sheets – Best Concatenation Options

CONCAT() and & do the same thing, so in that case there isn't a difference between them (just personal preference I suppose)

CONCATENATE() can also be used to join 2 items, however it's typically used to join greater than 2 terms.

Which one is best? I'd say whichever option makes the formula the most readable, in this case I would say CONCAT() only because you can easily identify which function you're running, and the terms "TEST" and B2 are clearly defined as the arguments.

Google Sheets Concatenate – How to Concatenate Several Columns into One

You need to filter down to non-empty cells, and then stack the results together:

={filter(A:A, len(A:A)); filter(B:B, len(B:B)); filter(C:C, len(C:C))}

This is the same as this answer by grayob which is unfortunately buried under outdated answers.

Best Answer

Related Solutions

Google Sheets – Best Concatenation Options

Google Sheets Concatenate – How to Concatenate Several Columns into One

Related Topic