Copie todos os dados numéricos das células de uma coluna para uma única célula

Question

Qual é a fórmula do Excel para concatenar todos os dados numéricos das células (poucas células contêm dados numéricos e poucas contém alfabetos e eu quero concatenar somente dados numéricos) de uma coluna em outra única célula e valores separados por vírgulas? Por exemplo, como mostrado abaixo, a coluna Q contém dados como este e eu quero o / p como mostrado na célula R2

Column Q 

1111111111                
Developing    
Developing    
Developing    
1111111112    
Developing    
1111111113    
Developing    
Developing


R2 cell

1111111112,1111111112,1111111113

    

Answer 1

Se você estiver usando o Office 365 Excel, poderá usar essa fórmula de matriz:

=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))),""))

Sendo uma fórmula de matriz, ela precisa ser confirmada com Ctrl-Shift-Enter em vez de Enter ao sair do modo de edição.

Que concatenará qualquer um na coluna Q que sejam números.

Como as fórmulas de matriz devem limitar as referências de dados, o:

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

localiza automaticamente a última célula na coluna Q que contém um número ou texto. Isso faz com que as iterações sejam limitadas apenas aos dados e permitam o crescimento ou o encolhimento do conjunto de dados sem a necessidade de redefinir as referências da fórmula.

    

Answer 2

Se você não tiver JOINTEXT() em sua versão do Excel, tente a seguinte função definida pelo usuário:

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