How to Convert a 2D Range to a Single Column or Row?
Convert Table to Column/Row is a Google Sheets function that converts a specified 2d range into a single column or row while ignoring any blank cells.. Formula Genius generates and validates this formula automatically from a plain-English prompt.
Transforming data from a 2D table into a single dimension can streamline your analysis. This formula helps you achieve that effortlessly.
The Formula
"Convert a 2D range of data into a single column or single row, ignoring blanks"
=FILTER(FLATTEN(A1:C3), FLATTEN(A1:C3) <> "")This formula converts a specified 2D range into a single column or row while ignoring any blank cells.
Step-by-Step Breakdown
- FLATTEN function transforms the 2D range into a single array.
- FILTER function filters out any blank entries from the flattened array.
- Specify the range (e.g., A1:C3) to define the data you want to convert.
- The condition (FLATTEN(A1:C3) <> "") ensures only non-blank values are returned.
Edge Cases & Warnings
- The range contains only blank cells, resulting in an empty output.
- The range includes mixed data types (text, numbers), which are all returned correctly.
- The range is larger than the visible area, but the formula still processes all data.
- Using the formula on a dynamic range that changes size may yield unexpected results if not adjusted.
Examples
"A1:C3 contains: {1, , 3; , 5, ; 7, 8, 9}"
{1; 3; 5; 7; 8; 9}"A1:C3 contains: { , , ; , , ; , , }"
{}Frequently Asked Questions
Can I use this formula with larger ranges?
Yes, just adjust the range in the formula to include more cells.
What if my data has formulas that return blank?
The formula will still ignore those cells as long as they evaluate to blank.
Is there a limit to how many cells I can flatten?
The limit is based on Google Sheets' overall cell limit, which is 10 million cells.
Can't find what you need?
Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.