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:
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:
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.