Ir para o conteúdo principal

Como concatenar texto facilmente com base em critérios no Excel?

Supondo que eu tenha uma coluna de números de ID que contém algumas duplicatas e uma coluna de nomes, e agora, eu quero concatenar os nomes com base nos números de ID exclusivos conforme a imagem à esquerda mostrada, para combinar rapidamente o texto com base em critérios, como poderíamos fazer no Excel?

doc combinar texto com base nos critérios 1

Concatenar texto com base em critérios com função definida pelo usuário

Concatene texto com base em critérios com Kutools para Excel


Para combinar texto com os números de ID exclusivos, você pode extrair os valores exclusivos primeiro e, em seguida, criar uma função definida pelo usuário para combinar os nomes com base no ID exclusivo.

1. Pegue os dados a seguir como exemplo, você precisa extrair os números de ID exclusivos primeiro, aplique esta fórmula de matriz: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Insira esta fórmula em uma célula em branco, D2 por exemplo, e pressione Ctrl + Shift + Enter chaves juntas, veja a captura de tela:

doc combinar texto com base nos critérios 2

Dica: Na fórmula acima, A2: A15 é o intervalo de dados da lista de onde você deseja extrair valores exclusivos, D1 é a primeira célula da coluna em que você deseja colocar o resultado da extração.

2. E, em seguida, arraste a alça de preenchimento para baixo para extrair todos os valores únicos até que os espaços em branco sejam exibidos, veja a captura de tela:

doc combinar texto com base nos critérios 3

3. Nesta etapa, você deve criar um Função definida pelo usuário para combinar os nomes com base nos números de identificação exclusivos, mantenha pressionado o ALT + F11 chaves, e abre o Microsoft Visual Basic para Aplicações janela.

4. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: concatene texto com base em critérios

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
    ConcatenateIf = CVErr(xlErrRef)
    Exit Function
End If
For i = 1 To CriteriaRange.Count
    If CriteriaRange.Cells(i).Value = Condition Then
        xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
    End If
Next i
If xResult <> "" Then
    xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

5. Em seguida, salve e feche este código, volte para sua planilha e insira esta fórmula na célula E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , veja a captura de tela:

doc combinar texto com base nos critérios 4

6. Em seguida, arraste a alça de preenchimento para baixo até as células às quais deseja aplicar esta fórmula, e todos os nomes correspondentes foram combinados com base nos números de ID, consulte a captura de tela:

doc combinar texto com base nos critérios 5

Dicas:

1. Na fórmula acima, A2: A15 são os dados originais que você deseja combinar com base, D2 é o valor exclusivo que você extraiu e B2: B15 é a coluna de nome que você deseja combinar.

2. Como você pode ver, combinei os valores que são separados por vírgula, você pode usar qualquer outro caractere alterando a vírgula “,” da fórmula conforme necessário.


Se você tem Kutools for Excel, Com o seu Combinar linhas avançadas utilitário, você pode concatenar de forma rápida e conveniente a base de texto em critérios.

Kutools for Excel : com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias.

Depois de instalar Kutools for Excel, siga as seguintes etapas:

1. Selecione o intervalo de dados que você deseja combinar com base em uma coluna.

2. Clique Kutools > Unir e dividir > Combinar linhas avançadas, veja a captura de tela:

3. No Combinar linhas com base na coluna caixa de diálogo, clique na coluna ID e clique em Chave primária para tornar esta coluna a coluna-chave na qual seus dados combinados se baseiam, consulte a captura de tela:

doc combinar texto com base nos critérios 7

4. E, em seguida, clique em Nome coluna para a qual deseja combinar os valores e clique em Combinar opção e escolha um separador para os dados combinados, consulte a captura de tela:

doc combinar texto com base nos critérios 8

5. Depois de terminar essas configurações, clique em OK para sair da caixa de diálogo, e os dados na coluna B foram combinados com base na coluna-chave A. Veja a captura de tela:

doc combinar texto com base nos critérios 9

Com este recurso, o seguinte problema será resolvido o mais rápido possível:

Como combinar várias linhas em uma e somar duplicatas no Excel?

Baixe e teste grátis Kutools para Excel agora!


Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Melhores ferramentas de produtividade de escritório

🤖 Assistente de IA do Kutools: Revolucionar a análise de dados com base em: Execução Inteligente   |  Gerar Código  |  Crie fórmulas personalizadas  |  Analise dados e gere gráficos  |  Invocar funções do Kutools...
Recursos mais comuns: Encontre, destaque ou identifique duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios    VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas  |  Mover colunas  |  Alternar status de visibilidade de colunas ocultas  |  Compare intervalos e colunas ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula    Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células    Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Palavras, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   ... e mais

Aprimore suas habilidades de Excel com o Kutools para Excel e experimente uma eficiência como nunca antes. Kutools para Excel oferece mais de 300 recursos avançados para aumentar a produtividade e economizar tempo.  Clique aqui para obter o recurso que você mais precisa...

Descrição


Office Tab traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (38)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Great function! Is there a way to maintain the format in the cell it's concatenating data from? i.e. $45.07, $555.34, $0.00, $0.25, -$12.25 I've figured out how to stack them with wrap text and CHAR(10) in place of "," but having trouble keeping the format. I will be using this for a mail merge in Word.
This comment was minimized by the moderator on the site
Hello, Laurie,If you want to keep the cell formatting when concatenating the data, you can apply the Advanced Combine Rows feature of Kutools for Excel, in the dialog box, after finishing the settings, you just need to check Use formatted values option, and all the data formatting will be kept as you need.
You can download Kutools for Excel and free trial 30-day.
This comment was minimized by the moderator on the site
Is there any way to add "and" instead of "," before the last data? (For example: D2355, D2273, D2397, D2600 and D2386)
This comment was minimized by the moderator on the site
Hi, Hossain,May be there is not a direct method for solving your problem, you can add another formula to convert the last comma to the text "and".=SUBSTITUTE(E2,","," and ",LEN(E2)-LEN(SUBSTITUTE(E2,",","")))
Please try, thank you!
This comment was minimized by the moderator on the site
It worked like a charm sir. Thank you so much.
This comment was minimized by the moderator on the site
Great function, exactly what I needed! Works like a charm
This comment was minimized by the moderator on the site
Hi,

Very helpful VBA solution. Thank you kindly! My question is: Is there a way to change the code or function for multiple criteria? Although the code works for me, I need it to show values corresponding to a timestamp-interval (>= timestamp A, <= timestamp B)


Thank you in advance. :)
This comment was minimized by the moderator on the site
Is there a way to assign this to a button? On large data ranges it takes a while, so ideally I only want it to start the concatenate process once I've finished doing everything else in the sheet. I tried adding a trigger myself but it stopped working completely
This comment was minimized by the moderator on the site
BTW i used the VBA solution
This comment was minimized by the moderator on the site
Extremely helpfull! After editing it for my sheet i have #VALUE! for some of the unique values.
I did a countif to see if it could be that there are too many names to concatenate. The two unique values that have the #VALUE! error have 13635 and 19810 results. Is there a way to overcome this?
This comment was minimized by the moderator on the site
How can I ignore blank cells? mine currently displays this:

";;;;;;;;;"

I'd like for the 1st, 3rd and last 3 semi colons not to there/show. TIA
This comment was minimized by the moderator on the site
Hello, Chantelle
When concatenating the cell values ignoring the blank cells, please apply the below User Defined Function:

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function

Please try it, hope it can help you!
This comment was minimized by the moderator on the site
thank you very much! This was so simple and helped a lot!!
This comment was minimized by the moderator on the site
Is it possible to replace the comma splitter with a line break, i.e. char(10)? Many thanks.
This comment was minimized by the moderator on the site
Hello, David,

To combine the cells with line break, the following User Defined Function may help you.

Function ConcatenateIf_LineBreak(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For I = 1 To CriteriaRange.Count
If CriteriaRange.Cells(I).Value = Condition Then
xResult = xResult & vbCrLf & ConcatenateRange.Cells(I).Value
End If
Next I
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf_LineBreak = xResult
Exit Function
End Function

After pasting this code, then apply this formula: =ConcatenateIf_LineBreak(A2:A13,F2,B2:B13,",").

After getting the results with this formula, you should click the Wrap Text to get the correct results you need.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations