How to Concatenate Two Excel Columns into a Dynamic Array
Question details
The user wants to combine data from two separate columns using a text separator and output the result as a dynamic array that automatically spills down the column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining lists of text (such as first and last names) from two different columns using a specific separator, aiming to generate the result automatically for the whole list using a single array formula.
- Observed behavior
- The formula fails to spill properly, returning either a #REF! error, a #SPILL! error, or only outputting a single concatenated result due to implicit intersection.
Ensure you are using a version of the spreadsheet software that supports dynamic arrays (like Microsoft 365) and verify that all cells directly below your intended formula destination are completely empty to prevent #SPILL! errors.
Use the Ampersand (&) Operator with Cell Ranges
This is the most efficient method to combine two full columns, leveraging dynamic array capabilities to automatically spill the results down the sheet.
By defining the entire range instead of a single cell, modern spreadsheet software will calculate the concatenation for every row and spill the results into the adjacent empty rows.
If your software returns only a single value instead of a list, it may not support dynamic arrays, or it might be applying implicit intersection. If you encounter a #SPILL! error, it means the output range is blocked by existing data.
Click on the top empty cell where you want the combined list to begin (for example, cell O2).
Type the formula =M2:M10&"#"&N2:N10 into the formula bar. Replace M2:M10 and N2:N10 with your actual data ranges, and change "#" to your preferred separator.
Press the Enter key. The concatenated results will dynamically calculate and populate all the necessary cells below.
Concatenate Row by Row for Older Versions
Use this traditional method if your spreadsheet software does not support dynamic array formulas or if you only need to process a few rows.
Easily Concatenate Columns with WPS Spreadsheet
WPS Office provides powerful formula capabilities, including robust text concatenation and dynamic array support, allowing you to combine massive datasets without lag or complex workarounds.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the columns you wish to combine.
- 2. Select the destination: Click on an empty cell where you want the combined dynamic array to start.
- 3. Enter the formula: Type your array formula, such as =A2:A100&"-"&B2:B100 into the cell.
- 4. Press Enter to spill: Press Enter, and watch WPS Spreadsheet seamlessly fill the entire column without the need to drag the fill handle.
Frequently Asked Questions
Why is my dynamic array formula returning a #SPILL! error?
A #SPILL! error occurs when the formula attempts to output multiple values, but one or more cells in the required destination range contain data, spaces, or formatting. You must clear all the cells directly below your formula to allow the array to expand.
Why does my array formula only return one row of data?
If the formula only returns a single value, your spreadsheet version might not support dynamic arrays, or an implicit intersection operator (such as the @ symbol) has been automatically added to your formula. Check your formula bar to remove the @ symbol, or upgrade to a software version that supports arrays.
Can I use different separators like spaces or commas?
Yes, you can easily change the text inside the quotation marks in your formula. For example, use " " for a space, ", " for a comma and a space, or leave it as "" (no space) to merge the text directly.
Does the CONCAT function work with dynamic arrays?
The standard CONCAT or CONCATENATE functions merge multiple ranges into a single continuous text string rather than returning a spilled array of individual rows. To output an array row-by-row, you must use the ampersand (&) operator between the specific cell ranges.
