6

I need to take separate columns containing first and last names on one spreadsheet, and combine them into a single column on a different sheet. Right now my formula is

=CONCATENATE(IMPORTRANGE("URL", "A1:A3"), " ", IMPORTRANGE("URL", "B1:B3"))

What I would want is to get a single column of

First1 Last1  
First2 Last2  
First3 Last3

But instead I am getting

First1First2First3 Last1Last2Last3

Because the second argument in the IMPORTRANGE function is a string, I can't just direct it to one cell and then drag the function over multiple cells like I normally would when concatenating. Is there a way to accomplish what I want without copying multiple data sets into one spreadsheet and working from there?

6

This can be done with an arrayformula as follows:

=arrayformula(importrange("URL", "A1:A3") & " " & importrange("URL", "B1:B3"))

The inner part describes what should be done within one row; the arrayformula then says "do it for every row".

  • Thank you, this was great. Although, it appears as though ARRAYFORMULA was only part of the fix. When I added the ARRAYFORMULA to the outside of my formula I got the same formatting as before. However, when I removed the CONCATENATE and replaced it with the &s it formatted properly. – Jon Mar 3 '16 at 0:44
  • Yes, binary concatenation & is important here; concatenate just puts everything it's given together. – user79865 Mar 3 '16 at 0:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.