Combine two columns into another sheet

Karina.Perez

Simply trying to combine FirstName and LastName columns into a new sheet. 

=JOIN({FirstName}1:{LastName}1, "-")

This come out #UNPARSEABLE

 

 

L_123

there are a couple ways to so this, the best way is somewhat complex.

If you refer to the cells individually you are going to run out of intersheet links quickly, you only get 100 of them. In order to use the most out of your available links, you need to use ranges and parse what you want out of them based on their location.

To do this you will need to make 2 column references, i'll call them {first} and {last}

Now we can take a look at how to "Join" them together. First we can use an index formula to return the value from a location.

Example:

Raw Data:

 Row#  Column1

1                A

2                B

3                C

4                D

5                E

 

Formula =Index([Column1]1:[Column1]5,1)

will return: A

Formula =Index([Column1]1:[Column1]5,3)

will return: C

 

We can use this to parse the data out of our ranges, then concatenate them. The formula should look something like this

 

=index({first},1) + index({last},1)

Now this will work perfectly, but it will set you up for a lot of manual work, because you need to change the "1" for every single row to reflect the next value. instead what I recommend is to add another column that simply counts up (I'll call it "Counter"). You can either do an autonumber, or what I recommend instead, just type 1 in the first row, 2 in the second, highlight both and drag it down to count up.

 

Once that is created, just change both of the "1"s in the index formula to reference the "Counter" Column.

 

=index({first},Counter1)+index({last},Counter1)