Copy all cells numeric data of one column to single cell

Question

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

What Excel are you using? – Scott Craner Jan 25 '18 at 18:08 — Scott Craner, Jan 25 '18 at 18:08

score 0 · Answer 1 · answered Jan 25 '18 at 18:14

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.

I am getting result in R2 cell as:- 1111111111, Developing, Developing, Developing, 1111111112, Developing, 1111111113, Developing, Developing — rocky, Jan 26 '18 at 01:47
Then you did not use Ctrl-Shift-Enter when exiting edit mode. — Scott Craner, Jan 26 '18 at 04:04

score 0 · Answer 2 · answered Jan 25 '18 at 21:13

0

If you do not have JOINTEXT()in your version of Excel, then try the following User Defined Function:

Public Function SpliceNum(rng As Range) As String
    Dim r As Range, v As Variant
    For Each r In rng
        v = r.Value
        If v <> "" Then
            If IsNumeric(v) Then
                SpliceNum = SpliceNum & "," & v
            End If
        End If
    Next r
    SpliceNum = Mid(SpliceNum, 2)
End Function

answered Jan 25 '18 at 21:13

Gary's Student

19,396

I want excel formula only as I do not want to use user-defined functions. Also, my excel supports TextJoin() – rocky Jan 26 '18 at 01:50

Copy all cells numeric data of one column to single cell

2 Answers2

Linked