Excel – Copy all cells numeric data of one column to single cell

microsoft excelworksheet-function

What is the excel formula to concatenate all cells numeric data(few cells contain numeric and few contains alphabets data and I want concatenate of numeric data only) of one column into another single cell and values separated by commas?
For example, as shown below column Q contains data like this and I want o/p as shown in cell R2

Column Q 

1111111111                
Developing    
Developing    
Developing    
1111111112    
Developing    
1111111113    
Developing    
Developing


R2 cell

1111111112,1111111112,1111111113

Best Answer

If you are using Office 365 Excel then you can use this array formula:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER($Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1)))),$Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1))),""))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Which will concatenate any in column Q that are numbers.

Since array formulas should limit the data references, the:

$Q$2:INDEX(Q:Q,MAX(IFERROR(MATCH("zzz",Q:Q),1),IFERROR(MATCH(1E+99,Q:Q),1)))

automatically finds the last cell in column Q that has either a number or text in it. This makes it so the iterations are limited to only the data and allow for growth or shrinkage of the data set without the need to reset the formula references.

enter image description here

Related Question