Leçon 28 de 30: Les fonctions et formules de la catégorie "texte".

Dans la catégorie "texte", vous retrouverez  24 fonctions. Les 11 suivantes s'avèrent très utiles et vous sont présentées selon la fréquence d'utilisation. Vous trouverez aussi, sur cette page, différentes formules utilisant ces 11 fonctions.

Notes et commentaires

Chaîne de caractères
Une chaîne de caractères est un ensemble sur lequel vous n'effectuerez pas de calculs. Un nom, une phrase, un numéro de téléphone, un numéro de série (avec ou sans lettre) et un code postal sont tous des chaînes de caractères. Tout ensemble qui contient autre chose que des chiffres est une chaîne de caractères.

Concaténation
Assemblage de chaînes de caractères.

Exemples

& ou CONCATENER (CONCATENATE)

Supposons que la cellule "A1" contient le prénom "Pierre", que la cellule "B1" contient le nom "Leclerc" et que vous vouliez assembler les deux (concaténation), la formule suivante :
=A1 & B1 résultera en "PierreLeclerc"
pour ajouter un espace entre le prénom et le nom :
=A1 & " " & B1 résultera en "Pierre Leclerc", l'espace entre les guillemets étant ajouté au milieu de la chaîne.

Pour concaténer, j'utilise toujours l'éperluette "&", mais la fonction CONCATENER donne le même résultat :
=CONCATENER(A1;" ";B1)

EPURAGE (TRIM)

Quelques fois, quand vous importez des données de certaines vielles base de données dont les champs sont de longueur fixe, vous vous retrouvez avec des valeurs comprenant des espaces inutiles. Si la valeur de la cellule "A1" est "Pierre Leclerc     " la formule suivante dans "B1" :
=EPURAGE(A1) résultera en "Pierre Leclerc", les espaces à la fin étant éliminées. La fonction EPURAGE élimine les espaces du début, du centre (sauf l'espace unique) et de la fin d'une chaîne.

GAUCHE (LEFT)

Si dans la cellule "A1" se trouve le mot "Comptabilité", la formule suivante en "B1" :
=GAUCHE(A1;2) résultera en "Co"
=GAUCHE(A1;3) résultera en "Com"

DROITE (RIGHT)

Si dans la cellule "A1" se trouve le mot "Comptabilité", la formule suivante en "B1" :
=DROITE(A1;2) résultera en "té"
=DROITE(A1;3) résultera en "ité"

NBCAR (LEN)

Si dans la cellule "A1" se trouve le mot "Comptabilité 101", la formule suivante en "B1" :
=NBCAR(A1) résultera en 16 soit le nombre de caractères incluant l'espace.

CHERCHE ou TROUVE (FIND ou SEARCH)

Si dans la cellule "A1" se trouve le mot "Comptabilité 101", la formule suivante en "B1" :
=CHERCHE(" ";A1;1) résultera en "13", la position de l'espace dans la chaîne à partir de la gauche.

La différence entre CHERCHE et TROUVE est que cette dernière se préoccupe si les lettres recherchées sont majuscules ou minuscules.

Le premier argument spécifie la chaîne de caractère que vous recherchez (un espace dans l'exemple), le second argument est l'adresse de la cellule de référence et le troisième argument (facultatif) spécifie à quelle position commence la recherche (à partir de la gauche). J'utilise rarement le troisième argument.

STXT (MID)

Si dans la cellule "A1" se trouve le mot "Comptabilité", les formules suivantes dans la cellule "B1" :
=STXT(A1;2;2) résultera en "om"
=STXT(A1;5;4) résultera en "tabi"
=STXT(A1;2;3) résultera en "omp"

Le premier argument est l'adresse de la cellule de référence, le second argument est la position du premier caractère à inclure dans le sous-texte à partir de la gauche et le troisième argument est le nombre de caractères à inclure dans le sous-texte.

DROITE, GAUCHE et CHERCHE (RIGHT, LEFT et FIND)

Mon client avait dans la colonne "A" une série de noms et prénoms combinés. Il voulait, à l'aide de deux formules, placer le prénom dans la colonne "B" et le nom dans la colonne "C".

SI tous les noms et prénoms avaient le même nombre de lettres les formules seraient :
dans B1 =GAUCHE(A1,6) résultant en "Pierre"
dans C1 =DROITE(A1,7) résultant en "Leclerc"

Cependant, si vous voulez deux formules qui fonctionnent pour tous les noms (d'Alexandre Courtemanche à Jean Ré), il vous faut les formules suivantes qui cherchent d'abord la position de l'espace dans la chaîne de caractère :
dans la colonne "B" :
=GAUCHE(A1;CHERCHE(" ";A1)-1)
dans la colonne "C":
=DROITE(A1;CHERCHE(" ";A1)-1)

CELLULE, STXT et CHERCHE (CELL, MID et FIND)

Si vous souhaitez que le nom et le chemin d'un classeur apparaissent dans une cellule, utilisez la formule suivante :
=CELLULE("nomfichier")

si vous ne voulez que le nom du fichier :
=STXT(CELLULE("nomfichier",A1);CHERCHE("[";CELLULE("nomfichier",A1))
+1;CHERCHE("]"; CELLULE("nomfichier";A1))-CHERCHE("[";
CELLULE("nomfichier";A1))-1)

SI, NBCAR, GAUCHE et DROITE (IF, LEN, LEFT et RIGHT)

Mon client (américain) avait une colonne de milliers de codes postaux dans la colonne "J". Certains avaient le format "78411" et d'autres, le format "783809617". Il voulait une formule qui insèrerait un trait d'union entre le cinquième et le sixième chiffre pour les codes à 9 chiffres. Voici la formule : 
=SI(NBCAR(J1)=9;GAUCHE(J1;5) & "-" & DROITE(J1;4);J1)

Dans un autre cas, les numéros de série comprenaient un certain nombre de chiffres mais pouvaient avoir de 1 à 3 lettres comme suffixes. Par exemple 54643AAA, 345643BBB et 5676543CCC. Pour extraire la partie chiffrée de tous ces numéros de série (dans la cellule "A1"), la formule est la suivante:
=GAUCHE(A1;NBCAR(A1)-3)