Tutoriels > Les dossiers mDF-XLpages > Dossiers Fonctions & Formules > Dossier Fonctions Texte (17 articles)

Dossier Fonctions Texte (17 articles)

Publié par Mth le 02-07-2012 (3300 lectures)

Les Fonctions Texte permettent de manipuler les chaînes de caractères présentes dans les cellules ou plages de cellules.


Ce dossier regroupe les principales fonctions texte détaillées dans les Tutoriels « Fonctions & Formules »:

 

Fonctions Texte
Utilisation
CNUM()Convertir une chaîne de caractères représentant des nombres en une valeur numérique
MAJUSCULE(), MINUSCULE() et NOMPROPRE()Changer la casse d'une chaîne de caractères
DROITE() - GAUCHE()Extraire les premiers ou derniers caractères d'une chaîne
TEXTE()Convertir une valeur numérique en texte formaté
CONCATENER()Assembler plusieurs chaînes de texte en une seule
CAR() - CODE()Utilisation des codes caractères
SUBSTITUE()Remplacer une chaîne de texte par une autre
NBCAR()Compter les caractères d'une chaîne de texte ou d'une plage de cellules
CHERCHE() TROUVE()Retourner la position d'une chaîne de caractères
STXT()Extraire une chaîne de caractères au sein d'une autre
SUPPRESPACE()

Supprimer les espaces inutiles dans une cellule

(suivi de "Purger les espaces inutiles" abordant tous les types d'espaces)

EXACT()

Comparer deux chaînes de texte

(ainsi qu'un exemple d'utilisation dans la FAQ pour forcer la saisie en Majuscules ou Minuscules dans une cellule)

REPT()Répéter des caractères un certain nombre de fois
CTXT()Convertir un nombre en texte
T()Tester et renvoyer du texte
EPURAGE()Supprimer certains caractères indésirables
REMPLACER()Remplacer une chaîne de caractères par une autre

 

 

Les modes d'emploi de ces fonctions vous sont présentés au fil des tutoriels et des fichiers d'exercices corrigés qui les accompagnent. Ces fonctions peuvent être utilisées seules, mais on les trouve souvent combinées pour répondre à certaines questions.


Retrouvez ci-dessous une illustration d'une dizaine d'exemples courants:

 

 

Exemples:

Trait bleu

 



 Combien y a t-il de lettre i dans la cellule A1:

 

Excel Compter le nombre de lettres dans une cellule

 

Exemple de formule

=NBCAR(A2)-NBCAR(SUBSTITUE(A2;"i";""))

 

Commentaires:

NBCAR(A2) compte le nombre de caractères de la cellule A2, ici 10
SUBSTITUE(A2;"i";"") remplace tous les i de la cellule A2 par rien, ce qui donne Grboulle
NBCAR(SUBSTITUE(A2;"i";"")) soit NBCAR(Grboulle) permet de compter le nombre de caractères sans la lettre i, résultat= 8
La différence des deux soit 10 – 8 donne 2

 

Compter le nombre de mots (séparés par un espace):

 

Excel Nombre de mots dans une cellule

 

Une formule en B5

=NBCAR(A5)-NBCAR(SUBSTITUE(A5;" ";"")) + 1

 

Commentaires:

Même raisonnement que l'exemple précédent, NBCAR(SUBSTITUE(A5;" ";"")) compte le nombre de caractères sans les espaces, NBCAR(A5) compte le nombre total de caractères en A5. Le nombre d'espaces est la différence entre les deux, +1 pour avoir le nombre de mots.


Comment séparer les noms et prénoms dans deux colonnes différentes?

 

Exscel Séparer les noms et prénoms dans deux colonnes différentes

 

Une formule en B9

=STXT(A9;TROUVE("zzz";SUBSTITUE(A9;" ";"zzz";NBCAR(A9)-NBCAR(SUBSTITUE(A9;" ";""))))+1;99)

 

En C9

=GAUCHE(A9;TROUVE("zzz";SUBSTITUE(A9;" ";"zzz";NBCAR(A9)-NBCAR(SUBSTITUE(A9;" ";""))))-1)

 
Commentaires formule en B9:

Comme vu ci-dessus NBCAR(A9)-NBCAR(SUBSTITUE(A9;" ";"")) indique le nombre d'espaces cellule A9, ici 2

SUBSTITUE(A9;" ";"zzz";NBCAR(A9)-NBCAR(SUBSTITUE(A9;" ";""))) équivaut à
SUBSTITUE(A9;" ";"zzz";2), soit remplace le second espace de la chaîne par une chaîne de caractère unique qui n'existe probablement pas dans un nom: zzz, ce qui donne "de Saint-PierrezzzAndré"

La fonction TROUVE() permet d'indiquer la position de cette chaîne,
TROUVE("zzz";"de Saint-PierrezzzAndré") renvoie 16, on ajoute 1 pour trouver la position du premier caractère juste après zzz soit 17.

Enfin, la fonction STXT() va extraire de la cellule A9 les caractères à partir de la 17ème position, pour une longueur de 99 (la longueur pourrait-être calculée, mais ici 99 permet de raccourcir la formule tout en couvrant tous les prénoms possibles, il n'y a pas de prénoms supérieurs à 99 lettres)

 

Commentaires formule en C9:

La formule suit exactement le même raisonnement, mais extraire les 15 caractères de gauche avec la fonction GAUCHE() au lieu d'utiliser la fonction STXT()

 

 

 Comment extraire la partie numérique pour pouvoir faire des calculs?

Des montants en devises sont rapatriés d'un système central colonne A:

 

Excel Extraire des chiffres d'une cellule de texte

 

Une formule en B12:

=CNUM(SUBSTITUE(SUBSTITUE(DROITE(A12;NBCAR(A12)-CHERCHE(" ";A12;2));",";".");CAR(160);""))

 

Commentaires:

Cela ne se voit pas à l'écran, mais le premier caractère de chaque cellule est un espace, suivi de la devise sur 3 lettres puis à nouveau un espace. Autre subtilité qui ne se voit pas à l'écran, l'espace entre les chiffres n'est pas un espace classique mais un code caractère 160 hérité du système central.

CHERCHE(" ";A12;2) renvoie la position du 2ème espace après la devise, ici 5.
NBCAR(A12) donne la longueur totale de la chaîne, la différence entre les deux soit NBCAR(A12)-CHERCHE(" ";A12;2) donne 10, qui représente la longueur à extraire pour avoir tous les chiffres.
Ce que fait la fonction DROITE() qui extrait donc les 10 derniers caractères, pour obtenir "385 551,14"
Au sein de cette chaîne remplaçons la virgule par point avec la fonction SUBSTITUE(), nous obtenons "385 551.14", et remplaçons de même avec SUBSTITUE() notre caractère 160 par rien pour le supprimer. Nous obtenons cette chaîne : "385551.14"
Qu'il suffit de transformer en numérique avec la fonction CNUM().

Pour terminer et faciliter la lecture mettre un format de nombres dans la cellule (ici options régionales avec un point comme séparateur décimal)

 

Isoler le nom et le domaine d'une adresse mail

Excel Isoler le nom et le domaine d'une adresse mail

 

Une formule en B19

=GAUCHE(A19;CHERCHE("@";A19)-1)

 

 

Commentaires:

CHERCHE("@";A19) renvoie la position du caractère @ au sein de la chaîne de caractères, ici 9, on retranche 1 pour ne pas compter le caractère lui même.
La fonction GAUCHE() prend les 18 premiers caractères da la chaîne

 

Formule en C19

=DROITE(A19;NBCAR(A19)-TROUVE("@";A19))

 

Commentaires:

NBCAR(A19) renvoie la longueur totale de la chaîne ici 19
TROUVE("@";A19) indique la position du caractère @ soit 9
La différence entre les deux donne la longueur à extraire soit 10, la fonction DROITE() extrait donc les 10 derniers caractères de la cellule A19

 

 

Faire la somme de chiffres mélangés à des lettres

 

Des chiffres de prévision (précédés d'un P) et de réalisation sont indiqués colonne B

 

Excel Faire la somme de chiffres mélangés à des lettres

 

Une formule en B28:

=SOMME(CNUM(SUBSTITUE(B24:B27;"P";"")))

 

(formule matricielle à valider par CTRL MAJ ENTER)

 

Commentaires:

SUBSTITUE(B24:B27;"P";"") remplace tous les P de la plage par rien
La fonction renvoie une matrice en texte: {"4";"2";"3";"8"}
La fonction CNUM() le transforme en valeurs numériques {4;2;3;8}
La fonction SOMME() peut donc faire l'addition.

 

Mettre en forme des codes départements

 

Une liste de départements en colonne A est numérique, comment la transformer en données texte pour retrouver les zéros en première position?

 

Excel transformer des codes départements

 

Une formule en B32:

=REPT("0";2-NBCAR(A32))&A32

 

Ou

=CONCATENER(REPT("0";2-NBCAR(A32));A32)
Commentaires:
NBCAR(A32) donne la longueur de la chaîne cellule A32, soit 1 pour le ligne 32 ou 2 pour la ligne 34.
2-NBCAR(32) est donc égal à 1 (cas de la ligne 32) ou zéro (cas de la ligne 34)
REPT() va répéter le caractère 0 soit 1 fois (cas ligne 32) ou zéro fois (cas ligne 34)
Il suffit ensuite de concaténer ce résultat avec le contenu de la cellule colonne A pour obtenir systématiquement le numéro du département au format texte sur 2 positions.
 

Comment trouver la position du dernier espace?

 

Excel Trouver la position du dernier espace

 

Une formule en B39

=TROUVE("µµ";SUBSTITUE(A39;" ";"µµ";NBCAR(A39)-NBCAR(SUBSTITUE(A39;" ";""))))

 

Commentaires:

Comme vu ci-dessus NBCAR(A39)-NBCAR(SUBSTITUE(A39;" ";"")) indique le nombre d'espaces (ici 2)
SUBSTITUE(A39;" ";"µµ";NBCAR(A39)-NBCAR(SUBSTITUE(A39;" ";""))) revient à écrire SUBSTITUE(A39;" ";"µµ";2), et permet de remplacer le deuxième espace par des caractères suffisamment rares pour ne pas être présents cellule A2 ( µµ) ce qui permet d'obtenir cette chaîne: "Clair deµµlune"
Il ne reste qu'à trouver la position de µµ dans la chaîne ce que fait la fonction TROUVE()

 

 

Intégrer une date au bon format dans une phrase

 

La date du jour est inscrite en A41, comment obtenir la phrase en A42

 

Excel Intégrer une date dans une phrase

 

Une formule:

="Aujourd'hui nous sommes "&NOMPROPRE(TEXTE(A41;"jjjj"))

 

Commentaires:

TEXTE(A41;"jjjj") transforme la date inscrite en A41 en un texte formaté (ici format utilisé= JJJJ pour n'afficher que le jour, le résultat est lundi)
NOMPROPRE() permet de mettre la première lettre en majuscule pour obtenir Lundi
Il ne reste qu'à concaténer le début de la phrase "Aujourd'hui nous sommes " et le résultat de la formule

 

Extraire le nom et la rue d'une adresse

 

Les adresses d'origine (formatées avec un retour chariot) sont en colonne A

 

Excel Extraire le nom et la rue d'une adresse

 

Une formule en B45

=GAUCHE(A45;CHERCHE(CAR(10);A45)-1)

 

Commentaires:

CAR(10) est le code caractère correspondant au retour chariot,
CHERCHE(CAR(10);A45) indique la position de ce caractère. On enlève 1 pour ne pas compter le caractère lui même ce qui donne 14
La fonction GAUCHE() va extraire les 14 premiers caractères de la cellule A44.

 

Formule en C45:

=STXT(A45;CHERCHE(CAR(10);A45)+1;CHERCHE("µ";SUBSTITUE(A45;CAR(10);"µ";2))-CHERCHE(CAR(10);A45)-1)

 

Commentaires:

SUBSTITUE(A45;CAR(10);"µ";2)) remplace le deuxième retour chariot par un µ ce qui donne: "Bernard Dupont 3 rue des remisesµ01234 Trifouilly-les-oies"

CHERCHE("µ";SUBSTITUE(A45;CAR(10);"µ";2)) indique la position du µ soit 33
CHERCHE(CAR(10);A45)-1 indique la position du premier retour chariot soit 15, moins 1 pour ne pas compter le caractère lui même.
La différence entre les deux soit 33 – 14 donne la longueur de la chaîne à extraire, soit 17 caractères.

La fonction STXT() extrait donc de A45, à partir de la position 16 (calculée avec CHERCHE(CAR(10);A45)+1) , une chaîne de 17 caractères pour obtenir 3 rue des remises.

 

 

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

 

Autres articles dans cette catégorie Publié le Vues
Dossier Fonctions Texte (17 articles) 02-07-2012 3301
Dossier Références aux cellules ou plages (3 articles) 05-12-2008 4270