Comment manipuler du texte avec des fonctions sur excel ?

hipster or bearded man with long beard and stylish hair on thoughtful face in tie and white shirt on textured beige background, copy space

Cet article s’intéresse à la manipulation de caractères avec les fonctions de texte d’EXCEL. Il est courant de récupérer des données provenant d’un système informatique extérieur. Les informations sont alors souvent collées les unes aux autres dans le tableur.

Par exemple, vous pourriez importer une chaîne de caractères « 1110422 » qui signifierait le 22/04/2011 pour la société 1 ou bien la chaîne de caractères 2980422 qui signifierait dans votre langage métier le 22/04/1998 pour la société 2.

Excel met à votre disposition de nombreuses fonctions qui permettent d’extraire des morceaux de texte à partir d’une chaîne de caractères.

Voici cinq fonctions qui pourront vous servir :

• =DROITE(cellule ; nombre de caractères) : vous extrayez un nombre de caractères à partir de la droite.

Par exemple, si A5 contient le texte « PARIS 75009 », l’expression =DROITE(A5;5) extrairales 5 caractères à partir de la droite de la cellule A5 et renverra 75009.

• =GAUCHE(cellule ; nombre de caractères) : vous extrayez un nombre de caractères à partir de la gauche.

Par exemple, si A5 contient le texte « PARIS 75009 », l’expression =GAUCHE(A5;5) extrairales 5 caractères à partir de la gauche de la cellule A5 et renverra PARIS.

• STXT(cellule, départ, nombre de caractères) : vous extrayez de la cellule un nombre de caractères en partant d’une position donnée.

Par exemple, si A5 contient le texte « PARIS 75009 », l’expression =STXT (A5;3;4) extraira4 caractères à partir du 3^ème caractère et renverra la chaîne « RIS 7 ».

• =TROUVE(critère ; cellule) : Cette fonction vous renvoie la position du critère dans la cellule.

Par exemple, si A5 contient le texte « PARIS 75009″,  l’expression =TROUVE( » « ,A5) cherche le premier espace dans la cellule A5 et renvoie la valeur 6 qui indique sa position dans la chaîne de caractères.

L’expression =TROUVE(« 9 »;A5) renverra la valeur 11 puisque le 9 se trouve à la 11^ème position dans la chaîne de caractères.

• =CNUM(« chaîne numérique ») transforme la chaîne numérique en nombre.

Par exemple, si la cellule A5 contient la chaîne ‘520 (et vous pouvez constater une apostrophe devant le nombre), la valeur 520 sera aligné à gauche parce que 520 sera considéré comme du texte par Excel. Si vous écrivez dans une autre cellule l’expression =CNUM(A5), la valeur 520 sera alors automatiquement alignée à droite dans la cellule.

Transformer une chaîne de caractères en date

Voici un groupe de caractères stockés dans la cellule A1: G110422. Nous voulons récupérer les deux caractères « 11 » et les transformer en 2011, puis récupérer les 4 caractères « 0422 » et les transformer en 22/04, puis par un coup de baguette magique, transformer le tout en 22/04/2011. Excel propose une fonction qui transforme en date trois chaînes de caractères.

• L’expression =DATE(« année »; »mois »; »jour ») transforme des données texte en date.

Par exemple, l’expression =DATE(« 2011″; »04″; »22 ») renverra 22/04/2011.

Nous devrons donc extraire les caractères de « G110422 » pour les mettre dans la fonction DATE. Mettons la chaîne « G110422 » dans la cellule A2.

L’expression =DATE(« 20 »& STXT(A2;2;2);STXT(A2;4;2);DROITE(A2;2)) renverra 22/04/2011. Si Excel n’affiche pas la date, vous devrez modifier le format d’affichage en choisissant le format date courte.

L’expression « 20 » & STXT(A2;2;2) indique que la chaîne « 20 » est collée au résultat de la fonction STXT, c’est à dire la chaîne « 11 »

On aurait pu écrire également : CONCATENER(« 20 »;STXT(A2;2;2))

Voici l’expression finale avec la fonction CONCATENER qui signifie coller : =DATE(CONCATENER(« 20 »;STXT(A2;2;2));STXT(A2;4;2);DROITE(A2;2))

Un SI pour tester des caractères

Considérons la cellule A1 contenant les chaînes ‘110422 et ‘980422. La première signifie  le 22/04/2011 et la deuxième le 22/04/1998. Nous voulons qu’ Excel fasse un test sur les 2 premiers caractères.

Si ces deux premiers caractères sont compris entre 90 et 99, alors la date finale devra avoir la forme 22/04/19XX et dans le cas contraire la date finale devra avoir la forme 22/04/20XX.

L’expression suivante transforme les deux premiers caractères en numérique grâce à la fonction CNUM. La fonction ET permet de réaliser un test entre deux valeurs à l’intérieur d’une fonction SI.

• =SI(ET(CNUM(STXT(A1;1;2))>=90;CNUM(STXT(A1;1;2))<=99);« 19 »;« 20 »)

L’expression suivante intègre la précédente dans une fonction DATE.

• =DATE(SI(ET(CNUM(STXT(A1;1;2))>=90;CNUM(STXT(A1;1;2))<=99);« 19 »;« 20 »)&STXT(A1;1;2);STXT(A1;3;2);STXT(A1;5;2))

Extraire des prénoms et des noms séparés par un espace

La figure ci-dessous montre une liste de prénoms et de noms séparés par un espace dont la position est aléatoire. L’objectif est d’extraire les prénoms et les noms pour les placer dans deux colonnes séparés.

La façon de faire pourrait être la suivante :

• Récupérer la longueur de la chaîne de caractères grâce à la fonction NBCAR(A2)
• Repérer la position de l’espace grâce à la fonction TROUVE( » « ;A2)
• Extraire le prénom du début jusqu’à la position de l’espace moins 1, avec la fonction GAUCHE(A2,TROUVE( » « ;A2)-1)
• Extraire le nom en partant de la position de l’espace plus 1 sur la longueur totale de la chaîne avec la fonction STXT(A2;TROUVE( » « ;A2)+1;NBCAR(A2))

La figure ci-dessous montre les formules dans le tableau

Pour aller plus loin, vous pouvez retrouver ces ouvrages qui s’adressent à toutes les personnes qui souhaitent approfondir leurs connaissances sur Excel.

Vous recherchez une formation Excel ? Rendez-vous sur notre site www.cnfce.com / formation Excel