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: This function transforms an array or range of cells into a single row.