«

»

Fonctions Texte (Droite, Gauche, Stxt, NbCar, …)

Il est fréquent, dans Excel, de devoir réorganiser les données sur lesquelles vous travaillez. Il est en effet courant d’extraire une partie d’une cellule, de fusionner le contenu de plusieurs cellules (on parle de concaténation) ou de supprimer une partie des informations. Pour cela, les fonctions Texte, vont nous être très utiles.

Nous allons prendre comme exemple tout au long de cet article, un travail sur les numéros de sécurité sociale français. Un numéro de sécurité sociale est unique pour chaque individu et se décompose de la façon suivante.

fonction-texte_1

Depuis la version d’Excel 2013, un nouvel outil d’extraction appelé le remplissage instantané vous permet d’extraire des sous-chaînes de texte dans écrire de formule. C’est à la fois magique mais aussi dangereux. En fait, il est important de connaître et les fonctions de texte et le remplissage instantané.

Regrouper (concaténer) du texte

Pour regrouper du texte dans Excel il existe trois fonctions ainsi que le signe & (et commercial). Historiquement, il y a toujours eu la fonction CONCATENER, mais cette fonction est très limitée et surtout est très facilement remplaçable par le symbole &. Depuis la version d’Excel 2016, deux nouvelles fonctions CONCAT et JOINDRE.TEXTE sont venues enrichir les possibilités de regroupement de texte dans Excel.

Signe & ou CONCATENER

Le signe & permet de regrouper le contenu de 2 cellules pour n’en faire qu’une seule. Ainsi, pour associer le contenu de la cellule B2 avec la cellule C2 nous allons écrire en A2.

fonction-texte_2

=B2&C2

Et le résultat en A2 devient le suivant. Vous pouvez noter au passage que le critère Sexe est de type numérique (cadré à droite) et que le résultat final est de type Texte (cadré à gauche).

 

Maintenant, si vous voulez mettre un symbole de délimitation entre ces 2 valeurs, il faut que vous le saisissiez manuellement.

fonction-texte_3

=B2& »-« &C2

Dans ce cas il y a 2 fois le signe &

  • Une première fois pour faire la concaténation entre B2 et le tiret
  • Une seconde fois pour faire la concaténation en le tiret et C2

Ce que nous venons de faire avec le signe & se fait également avec la fonction CONCATENER. En plus, si vous devez sélectionner plusieurs cellules, il faut le faire une par une CONCATENET(B2;C2;D2;E2). Honnêtement, pourquoi s’embêter avec cette fonction si le signe & fait le même travail. Donc pour des regroupements simples (2 ou 3 colonnes), il est préférable d’utiliser le signe &

La fonction CONCAT

fonction-texte_4La fonction CONCAT est apparue dans la version de Mars 2016 d’Excel 2016 et corrige le défaut principal de la fonction CONCATENER à savoir qu’il est maintenant possible de sélectionner une plage de cellules plutôt que de sélectionner cellule par cellule.

Dans notre exemple nous pouvons facilement regrouper le numéro de sécurité sociale des personnes en écrivant la fonction suivante :=CONCAT(B2:G2)

La fonction JOINDRE.TEXTE

La fonction JOINDRE.TEXTE, apparue en Mars 2016, est plus riche que la fonction CONCAT car elle permet de concaténer des cellules en sélectionnant une plage mais en introduisant la possibilité d’insérer un délimiteur entre chacune des données. De plus, cette fonction vous donne la possibilité de ne pas prendre en compte les cellules vides (c’est très astucieux). Son écriture est la suivante

  • Délimiteur ( ou , ou ; enfin ce que vous voulez mais toujours entre guillemets)
  • Prise en compte des cellules vides (VRAI ou FAUX)
  • Plage de cellules à concaténer

fonction-texte_5Ainsi, si nous souhaitons constituer le numéro de sécurité sociale pour toute les personnes de notre liste, il nous suffit d’écrire la formule

=JOINDRE.TEXTE(« -« ;VRAI;B2:G2)

Extraction de données

L’extraction de données représente une part importante du travail effectué régulièrement dans Excel. Il est donc important de connaître et de maîtriser les fonctions qui extraient une partie d’une chaîne de caractères.

La fonction GAUCHE

La fonction GAUCHE récupère les n caractères qui commencent votre chaîne de caractères. Son écriture est la suivante :

=GAUCHE (Chaîne de caractères;n caractères)

Dans notre exemple, pour extraire le critère Sexe (1 seul caractère de long), il faut écrire

=GAUCHE(A2;1)

La fonction DROITE

La fonction DROITE récupère les n caractères qui terminent votre chaîne de caractères. Son écriture est la suivante

=DROITE(Chaîne de caractères;n caractères)

De la même façon pour extraire le numéro d’enregistrement au moment de la naissance (3 caractères de long), il faut écrire la formule

= DROITE(A2;3)

La fonction STXT

La fonction STXT isole certains caractères à l’intérieur d’une chaîne de caractères.

=STXT(Chaîne de caractères;position du début de la sélection;nombre de caractères à sélectionner)

Dans notre exemple, pour extraire le code du département (4ème élément du numéro de sécurité sociale) nous allons écrire

=STXT(A2;9;2)

La fonction NBCAR

La fonction NBCAR (ce qui signifie nombre de caractères) compte le nombre de signes (lettres, nombres, ponctuations, espaces) qui composent votre chaîne de caractères.

=NBCAR(Chaîne de caractères)

Dans notre exemple, le numéro de sécurité sociale fait 18 caractères (avec les tirets) ou 13 sans les tirets.

La fonction CHERCHE (ou TROUVE)

La fonction CHERCHE permet de trouver la position d’un ou plusieurs caractères spécifiques dans une chaîne de caractères. La fonction CHERCHE renvoie donc un nombre. La différence entre CHERCHE et TROUVE c’est que CHERCHE n’est pas sensible à la casse (ignore le fait que la chaîne recherchée comporte des majuscules ou pas). Par contre, pour la fonction TROUVE, les chaînes abcde et Abcde ne sont pas identiques.

Si la chaîne de caractères n’a pas été trouvée, la fonction renvoie une erreur. Comme je vous l’ai montré dans la vidéo, en cas d’erreur, on peut utiliser la fonction SIERREUR.

La fonction SUPPRESPACE

Cette fonction permet de retirer les espaces qui précèdent ou qui suivent une chaîne de caractères. De cette façon, vos chaînes de caractères sont parfaitement propres 🙂

Explication des formules complexes de la vidéo.




À la fin de la vidéo je vous ai présenté deux formules assez complexes. Il est important maintenant de prendre le temps de vous les expliquer.

Tout d’abord dans la première fonction,  il a fallu réaliser une soustraction entre le nombre total de caractères contenu dans la cellule (fonction NBCAR) et la position du mot à rechercher « cell:« , obtenu par la fonction CHERCHE.

=NBCAR(C4)-CHERCHE(« cell: »;C4)-4)

Je reconnais que cette solution est complexe mais comme la position du mot « Cell: » n’est pas constante, je suis obligé d’effectuer une soustraction entre le nombre total de caractères contenus dans la cellule avec la position de la chaîne recherchée. De cette façon il est plus facile ensuite d’extraire les informations qui suivent le mot « cell:« .

=SIERREUR(DROITE(C4;NBCAR(C4)-CHERCHE(« cell: »;C4)-4); » »)

 

Pour la seconde fonction, celle qui extrait le numéro de portable qui est situé au milieu d’une cellule, la fonction est plus complexe. Parce que nous devons d’abord déterminer la position du mot « cell: », point de départ de notre extraction, et la position du slash suivant qui va déterminer la fin de votre extraction.

Déterminer la position du mot « cell: » c’est facile car cela s’obtient par la formule suivante :

=CHERCHE(« cell: »;C4)

Maintenant pour trouver la position du « / » qui suit le mot « cell: », nous devons intégrer dans la fonction CHERCHE, un troisième paramètre ; la position qui suit la détection du mot « cell: ». La seule solution pour faire cela c’est d’écrire en 3e paramètre une nouvelle fonction CHERCHE qui se positionne au-delà du mot « cell: »  précédemment cherché.

=CHERCHE(« / »;C4;CHERCHE(« cell: »;C4)+5)

Pour déterminer le nombre de caractères à extraire avec la fonction STXT, il est indispensable de retrancher à la valeur trouvée par la précédente fonction, la position de départ du mot « cells: ».

(CHERCHE(« / »;C4;CHERCHE(« cell: »;C4)+5)-(CHERCHE(« cell: »;C4)+5))

Nous avons déterminé la position de départ, maintenant nous sommes capables de déterminer la longueur à extraire, il ne reste plus qu’à insérer tout cela dans une fonction STXT pour extraire un numéro de mobile qui se trouve entre les chaînes « cell: » et un « / ».

=SUPPRESPACE(SIERREUR(STXT(C4;CHERCHE(« cell: »;C4)+5;(CHERCHE(« / »;C4;CHERCHE(« cell: »;C4)+5)-(CHERCHE(« cell: »;C4)+5))); » »))

Comme cela a déjà été dit dans la vidéo, cette formule est vraiment trop compliquée. Il est donc important pour vous de veiller à avoir des données propres en entrée pour vous éviter de faire des fonctions aussi complexes que celle-ci.




Lien Permanent pour cet article : https://www.excel-exercice.com/fonctions-droite-gauche-stxt-nbcar-supprespace-concat-joindre/

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger