To extract numbers from a 6-column, 50-row table in Excel 2019 without dynamic arrays, you must use VBA User Defined Functions or Power Query. Standard formulas like TEXTJOIN cannot process array results directly in this version. This guide provides two robust solutions: a custom VBA function for immediate cell-based extraction and Power Query for automated data transformation.
Why Standard Formulas Fail in Excel 2019
Many users attempt to useTEXTJOIN combined with IF(ISNUMBER(...)) to concatenate numeric values from a range. While this approach works in Excel 365 due to dynamic array support, it fails in Excel 2019. In Excel 2019, ISNUMBER returns an array of TRUE/FALSE values when applied to a range. The IF function then returns an array of numbers or blanks. However, TEXTJOIN in Excel 2019 expects a single range reference, not an array result, causing a #VALUE! error.
Attempting to enter this as an array formula using Ctrl+Shift+Enter does not solve the issue because TEXTJOIN was introduced after the array calculation engine changes that would allow such nested array operations to pass cleanly into text concatenation functions. Therefore, we must rely on tools that can iterate through cells individually or transform the dataset structure entirely.
Solution 1: Using VBA User Defined Function
The most direct method for Excel 2019 users who need a formula-like experience is creating a custom VBA function. This function iterates through each cell in the specified range, checks if the value is numeric, and appends it to a string separated by commas. First, open your Excel workbook and press Alt + F11 to open the Visual Basic for Applications (VBA) editor. In the menu, select Insert > Module to create a new code module. Copy and paste the following code into the module window:Function ExtractNumbers(rng As Range) As String
Dim cell As Range
Dim result As String
For Each cell In rng
If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
' Ensure we are dealing with actual numbers, not text representations
If IsError(cell.Value) = False Then
If result = "" Then
result = CStr(cell.Value)
Else
result = result & ", " & CStr(cell.Value)
End If
End If
End If
Next cell
ExtractNumbers = result
End Function=ExtractNumbers(A1:F50)
This function will scan all 300 cells in the specified range. It ignores non-numeric entries such as text strings or errors. If multiple numbers are found, they are joined together with a comma and a space. This solution is efficient for static tables and requires no external dependencies beyond the VBA environment, which is standard in Excel 2019. Note that workbooks containing macros must be saved as .xlsm to preserve the function.
For more details on managing table structures in Office applications, see Word 365: Calculating Relative Row Values in Tables.
Solution 2: Using Power Query
If you prefer not to use VBA or require a solution that updates automatically when the source data changes, Power Query is the ideal alternative. Excel 2019 includes Power Query natively under the Data tab. Start by selecting your entire table range (A1:F50). Go to the Data tab on the ribbon and click From Table/Range. If prompted, confirm that your table has headers. This opens the Power Query Editor. In the Power Query Editor, you need to unpivot the columns so that all values are in a single column. Select all six data columns by clicking the first column header, holding Shift, and clicking the last. Right-click one of the selected headers and choose Unpivot Columns. This transforms the wide table into a long format with two columns: Attribute (original column name) and Value. Next, filter out the non-numeric values. Click the dropdown arrow on the Value column header. Uncheck the box for Type if it exists, or simply ensure that only numeric types remain. If your data contains mixed types, you may need to add a custom column to check for numbers, but typically, unpivoting allows you to filter by data type easily. Remove rows where the Value is null or text. Finally, go to the Home tab and click Close & Load. This loads the cleaned, numeric-only data back into Excel as a new table. You can then use simple aggregation functions likeSUM or AVERAGE on this clean list. This method is particularly useful if you need to perform further analysis on the extracted numbers rather than just listing them.
See also Creating a Formula on Excel for My Table for additional context on handling structured references.
Performance Considerations
When using the VBA function on large datasets, note that iterating through 300 cells is generally instantaneous. However, if you expand the range significantly or have thousands of such calls, performance may degrade. In such cases, Power Query is superior because it processes data in memory during the load phase rather than recalculating every time the sheet changes. Additionally, Power Query handles errors and missing data more gracefully than a basic VBA loop, which might crash if it encounters unexpected object types. Always test your chosen method with a small subset of your data first. Verify that the output matches your expectations regarding number formatting and delimiter choices. If you need to extract specific patterns of numbers, such as integers only or decimals up to two places, adjust the VBA logic accordingly or use Power Query's rounding features.Frequently Asked Questions
Can I use ARRAYFORMULA in Excel 2019?
No,ARRAYFORMULA is a Google Sheets function and does not exist in Excel. Excel uses implicit intersection and legacy array entry methods (Ctrl+Shift+Enter) instead. For complex array operations in Excel 2019, VBA or Power Query is required.
How do I handle negative numbers with these methods?
Both the VBA function and Power Query correctly identify negative numbers as numeric values. The VBA function will include the minus sign in the concatenated string. In Power Query, negative numbers are treated as valid numeric types and will appear in the final output table.What if my table has merged cells?
Power Query cannot read merged cells reliably. You should unmerge cells before importing data into Power Query. Similarly, the VBA function may skip merged cells depending on how Excel exposes their underlying values, so ensure your data range consists of individual, unmerged cells for accurate extraction.Is there a way to do this without VBA or Power Query?
You can manually copy the data to a helper column and use theVALUE() function wrapped in an IF(ISNUMBER(...)) check, but this requires manual iteration or helper columns for each row. There is no single native Excel 2019 formula that can extract and concatenate numbers from a multi-column range dynamically.
Does this work for currency formatted cells?
Yes. Cells formatted as Currency still contain numeric underlying values. The VBA function'sIsNumeric check will return True for currency values, and they will be extracted. Power Query will also recognize them as numeric types during the filtering step.