Chris a une feuille de calcul qui a des noms de client dans les colonnes A à F. Dans la colonne G, il veut inclure une formule qui prendra tous les noms des six colonnes de nom et les concaténera en une longue chaîne, avec les caractères // entre chaque nom . Il est possible qu’il n’y ait pas de noms dans les six colonnes et qu’il n’y ait pas de délimiteurs // de début ou de fin superflus.

La concaténation de texte dans Excel est facile. Par exemple, si vous avez quelque chose dans la cellule A2 et que vous souhaitez le concaténer avec ce qui est dans la cellule B2, vous pouvez le faire avec cette formule:

=A2 & B2

Vous pouvez inclure les // délimiteurs entre les deux valeurs en les ajoutant simplement au bon endroit:

=A2 & "//" & B2

C’est assez simple. En utilisant cette approche, vous pouvez concaténer les six noms en utilisant la formule suivante:

=A2 & "//" & B2 & "//" & C2 & "//" & D2 & "//" & E2 & "//" & F2

Là où les choses se compliquent, c’est lorsque vous reconnaissez que certaines de ces cellules peuvent ne rien contenir. Ainsi, la formule entraînerait soit des délimiteurs de fin ou de fin //, soit des doubles délimiteurs (////)

quelque part au milieu du résultat.

La solution évidente consiste à utiliser des instructions IF pour vérifier le contenu des cellules de nom avant de les concaténer. Cependant, cela peut entraîner des formules incroyablement longues. Par exemple, la formule suivante effectuera correctement la vérification et la concaténation:

=IF(RIGHT(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)),2)="//",LEFT(CONCATENATE(IF(A3="","", CONCATENATE(A3,"//")),IF(B3="","",CONCATENATE(B3,"//")), IF(C3="","",CONCATENATE(C3,"//")),IF(D3="","",CONCATENATE(D3,"//")), IF(E3="","",CONCATENATE(E3,"//")),IF(F3="","",F3)), LEN(CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))-2),CONCATENATE(IF(A3="","",CONCATENATE(A3,"//")), IF(B3="","",CONCATENATE(B3,"//")),IF(C3="","",CONCATENATE(C3,"//")), IF(D3="","",CONCATENATE(D3,"//")),IF(E3="","",CONCATENATE(E3,"//")), IF(F3="","",F3)))

Oui, c’est une formule sur une seule ligne. (Ouf!) Cette formule utilise l’approche d’imbrication des instructions IF pour obtenir le résultat souhaité. Cela peut fonctionner dans ce cas particulier, mais la formule est très proche de la limite d’Excel de n’autoriser que les instructions IF à être imbriquées à sept niveaux de profondeur.

La solution au problème potentiel des niveaux imbriqués consiste simplement à ne pas imbriquer les instructions IF. Au lieu de cela, vous pouvez évaluer chaque cellule individuellement et concaténer tout ce qui est renvoyé.

=MID(IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"")

& IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Notez que cette formule est beaucoup plus courte. Vous pouvez mieux voir ce qu’il fait si vous regardez la formule « éclatée » sur plusieurs lignes:

=MID( IF(ISTEXT(A3),"//"&A3,"") & IF(ISTEXT(B3),"//"&B3,"") & IF(ISTEXT(C3),"//"&C3,"") & IF(ISTEXT(D3),"//"&D3,"") & IF(ISTEXT(E3),"//"&E3,"") & IF(ISTEXT(F3),"//"&F3,""),3,2000)

Chaque instruction IF individuelle dans la formule évalue une cellule de nom et ne renvoie rien («  ») si la cellule ne contient pas de texte, ou elle renvoie le délimiteur (//) suivi du nom. La formule entière est ensuite incluse dans l’instruction MID qui coupe effectivement le premier // délimiteur de la chaîne.

Cette formule peut être encore plus raccourcie si, au lieu d’utiliser la fonction ISTEXT pour évaluer les cellules, vous faites simplement une comparaison booléenne pour savoir s’il y a du texte dans la cellule, comme suit:

=MID(IF(A3>"","//"&A3,"") & IF(B3>"","//"&B3,"") & IF(C3>"","//"& C3,"") & IF(D3>"","//"&D3,"") & IF(E3>"","//"&E3,"") & IF(F3>"","//"&F3,""),3,2000)

C’est exactement la même technique, juste un peu plus courte. (Et un peu plus court par rapport à la formule originale.)

Cette formule fonctionnera très bien, à condition que les valeurs dans les cellules de nom soient du texte. Si vos colonnes de nom contiennent des valeurs numériques pour une raison quelconque, vous pouvez facilement modifier la formule pour utiliser ISBLANK au lieu de ISTEXT, comme indiqué ici:

=MID(IF(ISBLANK(A3),"","//"&A3) & IF(ISBLANK(B3),"","//"&B3)

& IF(ISBLANK(C3),"","//"& C3) & IF(ISBLANK(D3),"","//"&D3)

& IF(ISBLANK(E3),"","//"&E3) & IF(ISBLANK(F3),"","//"&F3),3,2000)

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2173) s’applique à Microsoft Excel 97, 2000, 2002 et 2003.