TOCOL function

Release notes

Current Channel

Monthly Enterprise Channel

Semi-Annual Enterprise Channel

Semi-Annual Enterprise Channel (Preview)

Office for Mac

Returns the array in a single column.

Syntax

=TOCOL(array, [ignore], [scan_by_column])

The TOCOL function syntax has the following arguments:

array The array or reference to return as a column.

ignore Whether to ignore certain types of values. By default, no values are ignored. Specify one of the following:

• 0    Keep all values (default)
• 1    Ignore blanks
• 2    Ignore errors
• 3    Ignore blanks and errors

scan_by_column    Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values are ordered by row or by column.

Remarks

If scan_by_column is omitted or FALSE, the array is scanned by row; If TRUE, the array is scanned by column.

Errors

• Excel returns a #VALUE! when an array constant contains one or more numbers that are not a whole number.
• Excel returns a #NUM when array is too large.

Examples

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. If you need to, you can adjust the column widths to see all the data.

Example 1

Returns a single column from the array in range A2:D4 scanned by row which is the default.

Data
Ben	Peter	Mary	Sam
John	Hillary	Jenny	James
Agnes	Harry	Felicity	Joe
Formulas
=TOCOL(A2:D4)

Example 2

Returns a single column from the array in range A2:D4 scanned by row which is the default. Blank values return a 0.

Data
Ben	Peter	Mary	Sam
John	Hillary	Jenny	James
Agnes	Harry
Formulas
=TOCOL(A2:D4)

Example 3

Returns a single column from the array in range A2:D4 scanned by row which is the default. Blank values are ignored.

Data
Ben	Peter	Mary	Sam
John	Hillary	Jenny	James
Agnes	Harry
Formulas
=TOCOL(A2:D4,1)

Example 4

Returns a single column from the array in range A2:D4 scanned by column.

Data
Ben	Peter	Mary	Sam
John	Hillary	Jenny	James
Agnes	Harry
Formulas
=TOCOL(A2:D4,1,TRUE)