This function transforms an array or range of cells into a single column. TOCOL can scan values:
- By column, top to bottom
- By row, left to right
The scan_by_column
argument is a boolean value that controls how TOCOL reads values from the source array.
Sample Usage
TOCOL(A1:C3)
: A TOCOL
function that keeps all values and scans by row.
TOCOL(A1:C3, 1, TRUE)
: A TOCOL
function that ignores blanks and scans by column.
Syntax
TOCOL(array_or_range, [ignore], [scan_by_column])
array_or_range
: The array or range of cells to return as a column.[ignore]
: By default, no values are ignored. Specify one of these values:- 0: Keep all values
- 1: Ignore blanks
- 2: Ignore errors
- 3: Ignore blanks and errors
[scan_by_column]
: The boolean value ofscan_by_column
determines how the array is scanned. By default, theTOCOL
function scans the array by row.- True: Scans the array by column
- False: Scans the array by row
Examples
Use simple data transformation operation with TOCOL
Example data:
A |
B |
C |
|
---|---|---|---|
1 |
Ben |
Peter |
Mary |
2 |
John |
Hillary |
Jenny |
3 |
Agnes |
Harry |
Felicity |
Example: Input this formula in E1: =TOCOL(A1:C3)
Result:
E |
|
---|---|
1 |
Ben |
2 |
Peter |
3 |
Mary |
4 |
John |
5 |
Hillary |
6 |
Jenny |
7 |
Agnes |
8 |
Harry |
9 |
Felicity |
Ignore blanks with TOCOL
Example data:
A |
B |
C |
|
---|---|---|---|
1 |
Ben |
Peter |
Mary |
2 |
John |
|
Jenny |
3 |
Agnes |
Harry |
Felicity |
Example:Input this formula in E1: =TOCOL(A1:C3, 1)
Result:
E |
|
---|---|
1 |
Ben |
2 |
Peter |
3 |
Mary |
4 |
John |
5 |
Jenny |
6 |
Agnes |
7 |
Harry |
8 |
Felicity |
Scan by column with TOCOL
Example data:
A |
B |
C |
|
---|---|---|---|
1 |
Ben |
Peter |
Mary |
2 |
John |
Hillary |
Jenny |
3 |
Agnes |
Harry |
Felicity |
Example: Input this formula in E1: =TOCOL(A1:C3, 0, TRUE)
Result:
E |
|
---|---|
1 |
Ben |
2 |
John |
3 |
Agnes |
4 |
Peter |
5 |
Hillary |
6 |
Harry |
7 |
Mary |
8 |
Jenny |
9 |
Felicity |
Related functions
- TOROW function: This function transforms an array or range of cells into a single row.