Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 17 at 13:20

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 1 down vote accepted

This is what you are looking for:

=ARRAYFORMULA((IF(ROW(J:J)=1,"formulaTest",H1:H & I1:I)))
share|improve this answer
1  
Great! It finally works now!. Though I still don't know why I can not use "CONCATENATE", instead I need to use "&" in this formula. –  srjohnhuang Jan 17 at 6:53
    
@srjohnhuang With the CONCATNATE, the complete column range is fed to the ARRAYFORMULA. Using the ampersand, only the single entries are fed, giving you the desired result. –  Jacob Jan Tuinstra Jan 19 at 12:01
    
The reason you can use & but not concatenate() is that concatenate can take a range whereas & can only join specific discreet values. –  Matthew Quinlan Jun 30 at 18:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.