Vai al contenuto principale

Come concatenare facilmente il testo in base a criteri in Excel?

Supponendo di avere una colonna di numeri ID che contiene alcuni duplicati e una colonna di nomi, e ora, voglio concatenare i nomi in base ai numeri ID univoci come mostrato nell'immagine a sinistra, per combinare rapidamente il testo in base ai criteri, come potremmo fare in Excel?

doc combina il testo in base ai criteri 1

Concatena il testo in base a criteri con la funzione definita dall'utente

Concatena il testo in base a criteri con Kutools per Excel


Per combinare il testo con i numeri ID univoci, è possibile estrarre prima i valori univoci e quindi creare una funzione definita dall'utente per combinare i nomi in base all'ID univoco.

1. Prendi i seguenti dati come esempio, devi prima estrarre i numeri ID univoci, applica questa formula di matrice: =IFERROR(INDEX($A$2:$A$15, MATCH(0,COUNTIF($D$1:D1, $A$2:$A$15), 0)),""), Inserisci questa formula in una cella vuota, ad esempio D2, quindi premi CTRL + MAIUSC + INVIO tasti insieme, vedi screenshot:

doc combina il testo in base ai criteri 2

Consiglio: Nella formula sopra, A2: A15 è l'intervallo di dati dell'elenco da cui estrarre valori univoci, D1 è la prima cella della colonna in cui vuoi mettere in evidenza il risultato dell'estrazione.

2. Quindi trascina il quadratino di riempimento verso il basso per estrarre tutti i valori univoci finché non vengono visualizzati gli spazi, vedi screenshot:

doc combina il testo in base ai criteri 3

3. In questo passaggio, dovresti creare un file Funzione definita dall'utente per combinare i nomi in base ai numeri ID univoci, tenere premuto il tasto ALT + F11 chiavi e apre il file Microsoft Visual Basic, Applications Edition finestra.

4. Clic inserire > Modulie incolla il codice seguente nel file Moduli Finestra.

Codice VBA: concatena il testo in base a criteri

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. Quindi salva e chiudi questo codice, torna al tuo foglio di lavoro e inserisci questa formula nella cella E2, = CONCATENATEIF ($ A $ 2: $ A $ 15, D2, $ B $ 2: $ B $ 15, ",") , vedi screenshot:

doc combina il testo in base ai criteri 4

6. Quindi trascina il quadratino di riempimento verso il basso sulle celle a cui desideri applicare questa formula e tutti i nomi corrispondenti sono stati combinati in base ai numeri ID, vedi screenshot:

doc combina il testo in base ai criteri 5

Suggerimenti:

1. Nella formula sopra, A2: A15 sono i dati originali che desideri combinare in base, D2 è il valore univoco che hai estratto e B2: B15 è la colonna del nome che desideri combinare insieme.

2. Come puoi vedere, ho combinato i valori separati da virgola, puoi usare qualsiasi altro carattere cambiando la virgola "," della formula di cui hai bisogno.


Se si dispone di Kutools for Excel, Con la sua Combina righe avanzate utility, è possibile concatenare rapidamente e comodamente la base del testo sui criteri.

Kutools for Excel : con oltre 300 utili componenti aggiuntivi di Excel, da provare gratuitamente senza limitazioni in 30 giorni.

Dopo l'installazione Kutools for Excel, eseguire le seguenti operazioni:

1. Seleziona l'intervallo di dati che desideri combinare in base a una colonna.

2. Clic Kutools > Unisci e dividi > Combina righe avanzate, vedi screenshot:

3. Nel Combina righe in base alla colonna finestra di dialogo, fare clic sulla colonna ID e quindi su Chiave primaria per rendere questa colonna la colonna chiave su cui si basano i tuoi dati combinati, vedi screenshot:

doc combina il testo in base ai criteri 7

4. E poi clicca Nome colonna in cui si desidera combinare i valori, quindi fare clic su Combinare opzione e scegli un separatore per i dati combinati, vedi screenshot:

doc combina il testo in base ai criteri 8

5. Dopo aver terminato queste impostazioni, fare clic su OK per uscire dalla finestra di dialogo e i dati nella colonna B sono stati combinati insieme in base alla colonna chiave A. Vedi screenshot:

doc combina il testo in base ai criteri 9

Con questa funzione, il seguente problema verrà risolto il prima possibile:

Come combinare più righe in una e sommare i duplicati in Excel?

Scarica e prova gratuitamente Kutools per Excel ora!


Kutools for Excel: con più di 300 utili componenti aggiuntivi di Excel, liberi di provare senza limitazioni in 30 giorni. Scarica e prova gratuita ora!

I migliori strumenti per la produttività in ufficio

Funzioni popolari: Trova, evidenzia o identifica i duplicati   |  Elimina righe vuote   |  Combina colonne o celle senza perdere dati   |   Round senza formula ...
Super ricerca: VLookup a criteri multipli    VLookup a valori multipli  |   VLookup su più fogli   |   Ricerca fuzzy ....
Elenco a discesa avanzato: Crea rapidamente un elenco a discesa   |  Elenco a discesa dipendente   |  Elenco a discesa a selezione multipla ....
Gestore di colonna: Aggiungi un numero specifico di colonne  |  Sposta colonne  |  Attiva/disattiva lo stato di visibilità delle colonne nascoste  |  Confronta intervalli e colonne ...
Funzionalità in primo piano: Messa a fuoco della griglia   |  Vista di progettazione   |   Grande barra delle formule    Gestore di cartelle di lavoro e fogli   |  Resource Library (Testo automatico)   |  Date picker   |  Combina fogli di lavoro   |  Crittografa/decrittografa le celle    Invia e-mail per elenco   |  Super filtro   |   Filtro speciale (filtro grassetto/corsivo/barrato...) ...
I 15 migliori set di strumenti12 Testo Strumenti (aggiungi testo, Rimuovi personaggi, ...)   |   Più di 50 Grafico Tipi (Diagramma di Gantt, ...)   |   40+ Pratico Formule (Calcola l'età in base al compleanno, ...)   |   19 Inserimento Strumenti (Inserisci il codice QR, Inserisci immagine dal percorso, ...)   |   12 Conversione Strumenti (Numeri in parole, Conversione di valuta, ...)   |   7 Unisci e dividi Strumenti (Combina righe avanzate, Celle divise, ...)   |   ... e altro ancora

Potenzia le tue competenze di Excel con Kutools per Excel e sperimenta l'efficienza come mai prima d'ora. Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e risparmiare tempo.  Fai clic qui per ottenere la funzionalità di cui hai più bisogno...

scheda kte 201905


Office Tab porta l'interfaccia a schede in Office e semplifica notevolmente il tuo lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce ogni giorno centinaia di clic del mouse!
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