Saltar al contenido principal

¿Cómo concatenar fácilmente texto según criterios en Excel?

Supongamos que tengo una columna de números de identificación que contiene algunos duplicados y una columna de nombres, y ahora, quiero concatenar los nombres en función de los números de identificación únicos como se muestra en la captura de pantalla de la izquierda, para combinar rápidamente el texto según los criterios, ¿cómo podríamos hacer en Excel?

doc combinar texto según el criterio 1

Concatenar texto según criterios con la función definida por el usuario

Concatenar texto según criterios con Kutools para Excel


Para combinar texto con los números de identificación únicos, primero puede extraer los valores únicos y luego crear una función definida por el usuario para combinar los nombres basados ​​en la identificación única.

1. Tome los siguientes datos como ejemplo, primero debe extraer los números de identificación únicos, aplique esta fórmula de matriz: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Ingrese esta fórmula en una celda en blanco, D2 por ejemplo, luego presione Ctrl + Shift + Enter llaves juntas, vea la captura de pantalla:

doc combinar texto según el criterio 2

Consejo: En la fórmula anterior, A2: A15 es el rango de datos de la lista del que desea extraer valores únicos, D1 es la primera celda de la columna en la que desea sacar el resultado de la extracción.

2. Y luego arrastre el controlador de relleno hacia abajo para extraer todos los valores únicos hasta que se muestren los espacios en blanco, vea la captura de pantalla:

doc combinar texto según el criterio 3

3. En este paso, debe crear un Función definida por el usuario para combinar los nombres según los números de identificación únicos, mantenga presionada la tecla ALT + F11 llaves, y abre el Microsoft Visual Basic para aplicaciones ventana.

4. Hacer clic recuadro > Móduloy pegue el siguiente código en el Módulo Ventana.

Código de VBA: concatenar texto según criterios

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. Luego guarde y cierre este código, regrese a su hoja de trabajo e ingrese esta fórmula en la celda E2, = CONCATENAR SI ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , ver captura de pantalla:

doc combinar texto según el criterio 4

6. Luego arrastre el controlador de relleno hacia las celdas a las que desea aplicar esta fórmula, y todos los nombres correspondientes se han combinado en función de los números de identificación, vea la captura de pantalla:

doc combinar texto según el criterio 5

Consejos:

1. En la fórmula anterior, A2: A15 son los datos originales en los que desea combinar en función, D2 es el valor único que ha extraído y B2: B15 es la columna de nombre que desea combinar.

2. Como puede ver, combiné los valores que están separados por comas, puede usar cualquier otro carácter cambiando la coma "," de la fórmula según lo necesite.


Si tiene Kutools for Excel, Con su Filas combinadas avanzadas utilidad, puede concatenar rápida y convenientemente la base de texto en criterios.

Kutools for Excel : con más de 300 prácticos complementos de Excel, prueba gratuita y sin límite en 30 días.

Después de instalar Kutools for Excel, siga los siguientes pasos:

1. Seleccione el rango de datos que desea combinar en función de una columna.

2. Hacer clic Kutools > Fusionar y dividir > Filas combinadas avanzadas, ver captura de pantalla:

3. En la Combinar filas según la columna cuadro de diálogo, haga clic en la columna ID y luego haga clic en Clave primaria para hacer que esta columna sea la columna clave en la que se basan sus datos combinados, vea la captura de pantalla:

doc combinar texto según el criterio 7

4. Y luego haz clic Nombre columna en la que desea combinar los valores, luego haga clic en Combinar opción y elija un separador para los datos combinados, vea la captura de pantalla:

doc combinar texto según el criterio 8

5. Después de terminar esta configuración, haga clic en OK para salir del cuadro de diálogo, y los datos de la columna B se han combinado en función de la columna clave A. Ver captura de pantalla:

doc combinar texto según el criterio 9

Con esta función, el siguiente problema se solucionará lo antes posible:

¿Cómo combinar varias filas en una y sumar duplicados en Excel?

¡Descargue y pruebe Kutools para Excel ahora!


Kutools for Excel: con más de 300 prácticos complementos de Excel, prueba gratuita y sin límite en 30 días. ¡Descarga y prueba gratis ahora!

Las mejores herramientas de productividad de oficina

🤖 Asistente de IA de Kutools: Revolucionar el análisis de datos basado en: Ejecución inteligente   |  Generar codigo  |  Crear fórmulas personalizadas  |  Analizar datos y generar gráficos  |  Invocar funciones de Kutools...
Características populares: Buscar, resaltar o identificar duplicados   |  Eliminar filas en blanco   |  Combine columnas o celdas sin perder datos   |   Ronda sin fórmula ...
Super búsqueda: Búsqueda virtual de criterios múltiples    Búsqueda V de valores múltiples  |   VLookup en varias hojas   |   Búsqueda difusa ....
Lista desplegable avanzada: Crear rápidamente una lista desplegable   |  Lista desplegable dependiente   |  Lista desplegable de selección múltiple ....
Administrador de columnas: Agregar un número específico de columnas  |  Mover columnas  |  Toggle Estado de visibilidad de columnas ocultas  |  Comparar rangos y columnas ...
Características destacadas: Enfoque de cuadrícula   |  Vista de diseño   |   Gran barra de fórmulas    Administrador de hojas y libros de trabajo   |  Biblioteca de Recursos (Texto automático)   |  Selector de fechas   |  Combinar hojas de trabajo   |  Cifrar/descifrar celdas    Enviar correos electrónicos por lista   |  Súper filtro   |   Filtro especial (filtro negrita/cursiva/tachado...) ...
Los 15 mejores conjuntos de herramientas12 Texto Herramientas (Añadir texto, Quitar caracteres, ...)   |   50+ Tabla Tipos (Diagrama de Gantt, ...)   |   40+ Práctico Fórmulas (Calcular la edad según el cumpleaños, ...)   |   19 Inserción Herramientas (Insertar código QR, Insertar imagen desde la ruta, ...)   |   12 Conversión Herramientas (Números a palabras, Conversión de Moneda, ...)   |   7 Fusionar y dividir Herramientas (Filas combinadas avanzadas, Células partidas, ...)   |   ... y más

Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo.  Haga clic aquí para obtener la función que más necesita...

Descripción


Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
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