Dans ce tutoriel, vous découvrirez les formules avec et sans tableau pour supprimer les doublons des chaînes séparées par des virgules dans Google Sheets.
Il n’est pas nécessaire que le séparateur soit une virgule. Vous pouvez utiliser la formule si le séparateur est un pipe, un saut de ligne, etc.
Je vais vous expliquer quelles corrections vous devez apporter à la formule.
Vous avez peut-être déjà la formule pour supprimer les doublons des chaînes séparées par des virgules dans Google Sheets.
Mais avez-vous une formule avec tableau pour chaque ligne ?
Le but de la formule avec tableau ici est de supprimer les doublons des chaînes séparées par des virgules (pipe ou autre) de chaque ligne en résidant dans la ligne la plus haute de la plage.
J’ai deux types de formules avec tableau : une ancienne et une nouvelle basée sur les fonctions Lambda.
Suppression des doublons des chaînes séparées par des virgules : formule sans tableau
Non-Formule: =ArrayFormula(textjoin(« , « ,true,unique(trim(split(A2, », »)),true)))
Insérez la formule ci-dessus dans la cellule B2. Ensuite, copiez-la et collez-la dans la colonne jusqu’à atteindre la plage souhaitée.
Comment ça fonctionne ?
Le SPLIT divise le texte autour de la virgule et place chaque fragment dans une cellule distincte de la ligne.
Le TRIM supprime les espaces blancs.
Puis UNIQUE supprime les doublons. Le rôle de TEXTJOIN est de relier les fragments en une chaîne séparée par des virgules.
Le ArrayFormula est là pour supporter la fonction TRIM sans tableau.
La formule ci-dessus est une formule sans tableau pour supprimer les doublons des chaînes séparées par des virgules.
Si le séparateur est un pipe, remplacez split(A2,",") par split(A2,"|").
Dans TEXTJOIN, remplacez textjoin(", " par textjoin("|".
Passons maintenant à la formule avec tableau pour supprimer les doublons du texte séparé par des virgules.
Suppression des doublons des chaînes séparées par des virgules : formule avec tableau
Je vous suggère d’utiliser la formule Lambda qui est plus propre et fonctionne mieux.
Ancienne approche (codée avant Lambda) : ✖
La formule avec tableau est un peu plus longue. La voici :
Formule avec tableau (Ancienne école) : =ArrayFormula(transpose(split(textjoin(« , « ,1,IFERROR((if(row(indirect(« A2:A »&counta(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »)),flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »))<> » »))))))-match(REGEXEXTRACT(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »)),flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »))<> » »))), »w+ »),REGEXEXTRACT(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »)),flatten(split(if(len(A2:A),row(A2:A),)& » « &substitute(A2:A, », « , », »&row(A2:A)& » « ), », »))<> » »))), »w+ »),0)=1, »| »,)®EXREPLACE(to_text(unique(filter(flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »)),flatten(split(if(len(A2:A),row(A2:A),)& » « &trim(substitute(A2:A, », », » »&row(A2:A)& » « )), », »))<> » »))), »^(wW){1} », » »)))), »| »)))
Pour le fichier d’exemple avec la formule saisie (étape par étape), veuillez consulter la fin de l’article.
Comment cette formule supprime-t-elle les textes en double des chaînes séparées par des virgules ?
Nous pouvons expliquer la formule ci-dessus en 9 étapes réparties en trois catégories.
- Diviser les chaînes et les rendre uniques par ligne (3 étapes).
- Éléments pour combiner les chaînes en fonction de leurs lignes (4 étapes).
- Étapes finales pour supprimer les doublons des chaînes séparées par des virgules (2 étapes).
1. Diviser les chaînes et les rendre uniques par ligne
Les formules ci-dessous vont dans les cellules B2, C2 et D2. Consultez l’image ci-dessous pour comprendre ce qu’elles renvoient.
- Ajout de numéros de ligne et d’un espace blanc immédiatement après les délimiteurs.
=ArrayFormula(trim(substitute(A2:A,",",","&row(A2:A)&" ")))
- Ajout de numéros de ligne au début des valeurs de chaque ligne.
=ArrayFormula(if(len(A2:A),row(A2:A),)&" "&B2:B)
Le but des deux formules ci-dessus est d’ajouter des numéros de ligne à chaque chaîne.
- Diviser les chaînes en fonction du délimiteur, les rendre uniques et les filtrer.
=ArrayFormula(to_text(unique(filter(flatten(split(C2:C,",")),flatten(split(C2:C,","))<>""))))
Lorsque vous rendez les chaînes uniques de cette manière, la formule ne supprimera les doublons que de la même ligne.
Nous avons résolu un problème principal que nous pourrions rencontrer lors de la suppression de doublons des chaînes séparées par des virgules.
2. Éléments pour combiner les chaînes en fonction de leurs lignes
Les formules ci-dessous vont dans les cellules E2, F2, G2 et H2.
La formule dans l’étape précédente (étape n ° 3) renvoie les valeurs contenant les numéros de ligne uniques.
Les formules suivantes séparent les numéros de ligne (4e étape) et le reste des valeurs (5e étape). Veuillez vous référer à l’image ci-dessous.
- Extraction des numéros de ligne.
=ArrayFormula(REGEXEXTRACT(D2:D16,"w+"))
- Extraction des chaînes.
=ArrayFormula(REGEXREPLACE(D2:D16,"^(w*W*){1}",""))
Le but des deux formules suivantes (étapes n ° 6 et 7) est de retourner un séparateur pour marquer la fin de chaque ligne en fonction de la colonne D.
Nous y parviendrons en retournant le décompte en cours de sortie de l’étape n ° 4 et en remplaçant le numéro de séquence 1 par un pipe et les autres valeurs par des blancs.
- Décompte en cours en fonction des numéros de ligne extraits.
=ArrayFormula(row(A2:A16)-match(E2:E16,E2:E16,0))
- Délimiteur pour séparer les lignes.
=ArrayFormula(if(G2:G16=1,"|",))
Remarque : J’ai utilisé le caractère | ici. Si vos chaînes sont séparées par le même caractère, non pas une virgule, utilisez ici le caractère ~ à la place.
3. Formule finale qui supprime les doublons des chaînes séparées par des virgules
Veuillez consulter les colonnes F et H. Nous voulons ces colonnes dans l’étape finale. Tout d’abord, nous combinerons les séparateurs de pipe (ou de tilde) dans la colonne H avec les chaînes dans la colonne F.
Ensuite, nous les joindrons en utilisant une virgule comme séparateur, nous les diviserons au niveau du séparateur de pipe (ou de tilde) et les transposerons.
Les formules suivantes vont dans les cellules I2 et J2.
- Ajout de séparateurs aux chaînes extraites.
=ArrayFormula(H2:H16&F2:F16)
- Combinaison des chaînes en utilisant une virgule comme séparateur et division en fonction du séparateur.
=transpose(split(textjoin(", ",1,I2:I16),"|"))
De cette façon, nous pouvons supprimer les doublons des chaînes séparées par des virgules dans Google Sheets.
Remarque : Il ne devrait y avoir aucune ligne vide dans la plage. De plus, cette formule gourmande en ressources peut renvoyer des erreurs dans une plage de données plus importante.
Nouvelle approche (Lambda) : ✔
Formule avec tableau (Lambda - Nouvelle) : =map(A2:A,lambda(r,iferror(ArrayFormula(textjoin(« , « ,true,unique(trim(split(r, », »)),true))))))
Il suffit d’insérer ce code dans la cellule B2. Contrairement à la formule sans tableau, vous n’aurez pas besoin de la copier-coller vers le bas.
Elle se répandra automatiquement vers le bas s’il y a des cellules vides en dessous de la cellule B2.
C’est la même formule sans tableau que nous avons utilisée pour supprimer les doublons des chaînes séparées par des virgules.
La seule différence est l’utilisation de la fonction d’aide Lambda MAP.
La LHF aide la formule sans tableau à se répandre vers le bas.
Sample_Sheet_15521
Pour plus d’informations et d’astuces sur Google Sheets, rendez-vous sur Crawlan.com.
Prenez en compte qu’il ne doit pas y avoir de texte ci-dessous cette ligne.
