Concat() et Joindre.Texte()

Depuis des années, nous avions dans Excel une fonction dont l’utilité était pour le moins douteuse, la fonction Concatener(). En effet =concatener(B1;C1) est équivalent à =B1&C1. Pourquoi faire simple quand on peut faire compliqué ?

Nous avons maintenant, avec Excel 2019 et Office 365 une fonction qui remplace Concatener() tout en enrichissant ses fonctionnalités de façon notable, la fonction Concat(). Plus, en guise de prime, une toute nouvelle fonction – elle aussi dans le domaine de la gestion de chaînes de caractères – TextJoin().

Les fonctions Concatener() et Concat()

Dans l’exemple de la copie d’écran ci-dessous, nous avons entré les formules dans la colonne A et reproduit le texte de ces formules en colonne B. Comme le montrent clairement les cellules B1 et B2, il semble clair que l’utilisation de la fonction Concatener ne semble présenter aucun avantage par rapport au simple « & ».

La nouvelle fonction Concat() ne fait rient de plus, comme on peut le voir en ligne 3. On peut se demander quel est l’intérêt, à part l’économie de 4 caractères, de remplacer une fonction inutile par une autre fonction tout aussi inutile…

Syntaxe de la fonction Joindre.Texte()

La fonction Joindre.Texte(), en revanche, représente à la fois une réelle innovation et un apport utile.

La syntaxe est : Joindre.Texte(délimiteur;ignorer_si_vide;texte1;...)

Quand ignorer_si_vide est égal à 1 ou VRAI, on ignore les cellules vides.

La chaîne de caractères résultant ne peut pas dépasser 32 767 caractères. Au-delà, une erreur #VALEUR! est renvoyée.

Pour obtenir le même résultat avec cette nouvelle fonction qu’en A1:A3, il faut entrer en A4 la formule :

=joindre.texte(" ";0;C1:D1;":";E1)

Certes, ce n’est pas plus court que les formules précédentes, mais cette fonction ouvre de nouvelles possibilités dont nous verrons une illustration dans le prochain article.

Liste déroulante interactive (c)

Nous avons vu dans le dernier article comment limiter notre menu déroulant aux seules villes dont le nom contient une certaine chaîne de caractères, « el » en l’occurrence.

Ce qui est particulièrement intéressant, et auquel vous avez pu ne pas penser, c’est que l’on peut utiliser dans cette chaîne de caractères les deux fameux jokers, « ? » pour un caractère unique et « * » pour une chaîne de caractères de n’importe quelle longueur.

Utilisation du joker « ? »

Avec le joker « ? », si nous entrons la chaîne « o?o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par exactement un caractère.

Nous constatons qu’il n’y a que trois villes dans notre liste qui respectent cette condition car il n’y a pas d’ascenseur à droite :

Utilisation du joker « * »

Avec le joker « ? », si nous entrons la chaîne « o*o », nous obtenons toutes les villes dans lesquelles on peut trouver deux « o » séparés par un caractère ou par plusieurs caractères.

Le menu déroulant nous affiche les 7 premières villes respectant cette condition mais l’ascenseur à droite nous indique qu’il y en a d’autres encore…

Remarques sur les jokers

En règle générale, les deux jokers « ? » et « * » fonctionnent très bien pour localiser – et c’est vrai aussi dans Word ou dans Powerpoint – des chaînes de caractères, comme nous venons de le voir avec nos deux exemples.

On peut seulement regretter qu’il n’y ait pas en plus – quand on utilise la commande « Remplacer » – deux autres caractères signifiant que l’on veut garder les lettres concernées par le « ? » et le « * » exactement comme elles l’étaient auparavant. Hélas, Excel entre littéralement des « ? » et des « * » dans le texte remplacé.

Liste déroulante interactive (b)

Nous voyons ci-dessous à quoi notre modèle ressemble quand nous tapons « el » en E2 pour que la liste déroulante ne nous propose ensuite que les villes visibles en colonne H, celles qui contiennent cette chaîne de caractères.

Pour que cette liste déroulante fonctionne comme nous le désirons, il faut d’abord qu’elle soit alimentée par uniquement les villes retenues en colonne H. Il faut aussi que l’on ait le droit de taper « el » en E2, ce qui n’est pas possible avec une liste déroulante normale.

Alimentation de la liste déroulante

Pour alimenter la liste déroulante, il faut d’abord définir un nom dynamique.

C’est ce que nous faisons avec le nom « Ville », défini par :

=Ville!$H$2:decaler(Ville!$H$1;MAX(Ville!$G:$G);0)

…où « Ville » est le nom de notre feuille de calcul.

Il ne reste plus qu’à définir en E2 une zone de validation, en sélectionnant l’option « Liste » et en entrant =Ville dans le champ « Source ».

Libération de la liste déroulante

Il faut enfin « libérer » la liste déroulante pour qu’elle nous autorise à saisir une chaîne de caractères – telle que « el » – qui ne soit pas l’une des options de la liste déroulante.

Pour cela, lors de la définition de la zone de validation de la cellule E2, il suffit de sélectionner l’onglet « Alerte d’erreur » et de décocher l’option « Quand des données non valides sont tapées », qui est cochée par défaut :

Et voilà ! Le tour est joué, vous pouvez à présent entrer « el » en E2 et ne plus voir dans la liste déroulante que les villes dont le nom contient cette chaîne de caractères…

Liste déroulante interactive (a)

Le sujet que nous allons traiter à présent a déjà été traité dans ce blog il y a 5 ans. Je rencontre souvent des gens qui trouvent cette idée géniale et qui pourtant ne connaissaient pas cette technique. Je craque donc en vous la représentant de nouveau, mais avec un article plus approfondi.

Vous utilisez une liste déroulante mais vous rencontrez l’un des deux problèmes suivants :

la liste déroulante n’est pas triée, et il est donc difficile de localiser ce que vous recherchez ;

la liste déroulante est très longue, et il est fastidieux de trouver ce que vous recherchez.

Nous allons prendre pour exemple une liste que j’ai trouvée sur Internet dans laquelle les 120 villes les plus peuplées des Etats-Unis sont listées.

Comme le montre bien la copie d’écran, il n’est pas facile de trouver la position d’une ville – par exemple La Nouvelle-Orléans – quand on n’a aucune idée de sa population…

Un début de solution

Pour parvenir à la solution, il faut utiliser deux colonnes à droite.

En G2, la cellule G1 étant vide, nous avons entré la formule :

=G1+SI(estnum(cherche($E$2;A2));1;0)

Cette formule est alors reproduite jusqu'à la ligne 121, la dernière de la liste.

En H2, reproduite jusqu'à la ligne 121 :
=sierreur(index(A:A;equiv(ligne()-1;G:G;0));"")

Le but du jeu est de voir comment, en utilisant ces deux colonnes, vous pourrez définir une liste déroulante interactive qui, si vous entrez « el » en E2, ne liste que les villes contenant cette chaîne de caractères :