I have one array
1
2
3
another array
4
5
6
How do I use one function to join the 2 arrays?
1
2
3
4
5
6
Asked
Active
Viewed 5.9k times
41
7 Answers
88
Let's say your arrays are:
A1:A3 = {1;2;3} and B1:B3 = {4;5;6}
Write somewhere: ={A1:A3;B1:B3}
Semicolons ; are used to separate rows, and commas , are for columns.
Here is the documentation: Using arrays in Google Sheets
Luchostein
- 2,314
- 20
- 24
10
TRANSPOSE() //takes matrix as argument and returns transposed matrix
SPLIT() //breaks apart a string based on a delimiter character (char(13) here)
ARRAYFORMULA() //applies the formula within to array vs cell
CONCATENATE() //joins each cell in each range with a char(13), then the next cell...then the next matrix of cells (to which the first process also happens)
//note char(13) is a carriage return, i will call CR for ease
so if you have matrix A : 1, 2, 3 and matrix B : 4, 5, 6
the steps would look like this:
TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE("1CR2CR3CR" ; "4CR5CR6CR")), CR))
TRANSPOSE(SPLIT("1CR2CR3CR4CR5CR6CR", "CR"))
TRANSPOSE({"1","2","3","4","5","6"})
finally:
1
2
3
4
5
6
Pacerier
- 86,231
- 106
- 366
- 634
Mr.Monshaw
- 450
- 2
- 7
-
i saw that but couldn't understand how to translate it to my situation. could you translate it for me please? – jason Jul 26 '13 at 15:40
-
@Mr.Monshaw, Why is arrayformula needed in the first `transpose`? – Pacerier Nov 13 '18 at 00:31
3
=filter({A1:A;B1:B}, {A1:A;B1:B}<>"")
Masato Egami
- 31
- 1
-
3Welcome to StackOverflow! A code-only answer isn't very useful for other users searching for a solution. Could you add an explanation to what your code does? – Nander Speerstra Jan 17 '18 at 08:00
2
I'm noticing this is an older question, so this might not have existed when previous responses were written.
You're looking for the JOIN() formula.
Sample Usage
JOIN(" and-a ",{1,2,"1 2 3 4"})
JOIN(",",{1,2,3},{4;5;6})
JOIN("-",A1:A100)
Syntax
JOIN(delimiter, value_or_array1, [value_or_array2, ...])
delimiter - The character or string to place between each concatenated value.
delimiter may be specified as blank, e.g. JOIN(,{1,2,3}). value_or_array1 - The value or values to be appended using delimiter.
value_or_array2, ... - [ OPTIONAL ] - Additional value or array to be appended using delimiter.
Patrick_K
- 21
- 1
1
Given the information provided by Mr.Monshaw, I figured this out for you.
Given values "1,2,3" are in cells A1:A3 of a spreadsheet, and values "4,5,6,7" are in cells B1:B4. Here is the formula to use:
=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCATENATE(concat(A1:A3,",");concat(B1:B4,","))),","))
Explanation. The concat formula creates a string of the values with the indicated separator, ",". So concat(A1:A3,",") results in "1,2,3".
The Concatenate combines the values of the specified array as one string. So CONCATENATE(concat(A1:A3,",");concat(B1:B4,",")) results in "1,2,3,4,5,6,7"
The Split function here is used to identify the "," as a deliminator and not a value in the array. The Transpose will cause the results to display in a column instead of in a row.
user2054049
- 45
- 3
