How to Fix Excel CONCAT Not Combining Arrays Row by Row
Question details
The user needs to combine corresponding cells from two or more array ranges row by row, but the CONCAT function aggregates all elements into a single text string instead of performing pairwise concatenation.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Attempting to merge data from corresponding cells across multiple columns or arrays to create a new, combined array of text.
- Observed behavior
- Using the CONCAT function on multiple arrays merges every single value into one continuous text block rather than returning a new array structured row by row.
Check your spreadsheet software version to confirm if it supports dynamic arrays (like Microsoft 365, Office 2021, or newer), as older versions require legacy array formula execution.
Use the Ampersand (&) Operator for Dynamic Arrays
The most effective way to combine corresponding cells from two arrays row by row is to use the ampersand operator instead of the CONCAT function.
Unlike the CONCAT function, which squashes all array items together indiscriminately, the ampersand (&) operator forces the spreadsheet to evaluate the text concatenation pairwise, outputting an array of the same dimensions.
Click on the cell in your worksheet where you want the first combined text string to appear.
Type the formula using the ampersand operator, referencing your two arrays. For example, type =D1:D7&F1:F7.
Press Enter. The results will automatically spill down the column, perfectly combining the text row by row.
Use Legacy Array Formulas (Older Excel Versions)
If you are using Excel 2019 or older, dynamic arrays are not natively supported and require traditional CSE (Ctrl+Shift+Enter) execution.
Apply CONCAT to Individual Cells Using the Fill Handle
If you do not want to work with array formulas, you can perform the concatenation on a single row and copy the formula downward.
Easily Combine Array Data with WPS Spreadsheet
WPS Spreadsheet natively supports modern dynamic arrays, allowing you to quickly combine large datasets row by row without complex legacy formulas. Simply use the ampersand operator and let the software spill the results automatically.
- 1. Open your workbook: Launch WPS Office Spreadsheet and open the file containing the arrays you need to merge.
- 2. Enter the pairwise formula: Select the top cell of your destination column and type your array formula using the ampersand, such as =A1:A10&B1:B10.
- 3. Generate the results instantly: Press Enter. WPS Spreadsheet will automatically spill the concatenated strings row by row, accurately pairing your data.
Frequently Asked Questions
Why does the Excel CONCAT function merge all cells into one string?
The CONCAT function is designed to aggregate all provided text arguments into a single continuous string. When given an array or range, it processes every cell in the range sequentially and joins them together, rather than evaluating the operation pairwise.
What is the difference between CONCAT and CONCATENATE?
CONCAT is a newer function that can accept entire ranges (like A1:A5) and merge them into one large text block. CONCATENATE only accepts individual cell references or explicit strings (like A1, A2) and does not inherently process whole ranges.
What does it mean if my dynamic array formula returns a #SPILL! error?
A #SPILL! error occurs when a dynamic array formula needs to output results across multiple cells, but one or more of those target cells already contain data. To fix this, clear the cells where the formula intends to place the resulting text.
Can I use the TRANSPOSE function to combine arrays row by row?
Yes, in certain nested scenarios, formulas like =CONCAT(TRANSPOSE(D1:D7), TRANSPOSE(F1:F7)) might be suggested, but they can produce unexpected blocks of text depending on array dimensions. Using the direct ampersand operator (=D1:D7&F1:F7) is much simpler and more reliable.
