0
(0)
Sommaire

Dans la ligné de ces articles :

Un petit topo sur cette famille de fonctions.

Utiliser, générer un caractère

La fonction CAR

Permet de renvoyer le caractère de la table ASCII étendu (voir American Standard Code for Information Interchange) spécifié par sa valeur décimale (Rappel : on peut convertir d'hexadécimale à décimale avec la fonction HEXDEC).

= CAR(Nombre)
  • Nombre : Valeur décimale entière (unique ou matrice de valeurs) entre 1 et 255 (sauf 127, 129, 141, 143, 144, 157).
    • 1er caractère affichable Espace (32).

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante aux caractères désignés par Nombre.

La fonction UNICAR

Version étendue et plus récente de la fonction CAR. Renvoie le caractère Unicode (voir Unicode) spécifié par sa valeur décimale (Rappel : on peut convertir d'hexadécimale à décimale avec la fonction HEXDEC).

= UNICAR(Nombre)
  • Nombre : Valeur entière (unique ou matrice de valeurs) entre 1 et 200703 (30FFF) pour l'instant.
    • Attention : de nombreuses valeurs de cette plage n'ont pas de caractère correspondant.
    • 1er caractère affichable Espace (32).

Résultat : Valeur ou matrice de valeurs alphanumérique (texte) correspondante(s) au(x) caractère(s) désigné(s) par Nombre.

Exemples d'utilisation

En remplacement d'une image

Un visage souriant 😀 (1F600) ou non 😡 (1F621) en fonction des ventes.

= SI(Ventes<100;UNICAR(HEXDEC("1F621"));UNICAR(HEXDEC("1F600"))) => 😀 ou 😡 (sans couleurs)

Pour générer des retours à la lignes

On associe le contenu de 3 cellules (B2:B4) dans une seule cellule avec un retour à la ligne entre chaque valeur (nécessite d'activer aussi le paramètre "Renvoyer à la ligne automatiquement").

= JOINDRE.TEXTE(CAR(10);VRAI;B2:B4)
  • CAR(10) / UNICAR(10) est le caractère Retour à la ligne.
  • La fonction JOINDRE.TEXTE sera présenté dans l'article suivant (partie 2).

Comme séparateur

Les formules Excel n'acceptent page de générer des matrices/tableaux contenant d'autres matrices/tableaux.
Une astuce pour passer cette limitation est de remplacer des matrices imbriquées pas une chaîne constituée des valeurs concaténées de ces "sous-matrices".
Pour les exploiter, on utilisera la fonction FRACTIONNER.TEXTE mais pour cela il ne faudra pas se tromper quant aux caractères délimiteurs d'où l'importance de bien le choisir sans risque de confusion avec un caractère déjà présent dans les chaînes.

On peut choisir un caractère ayant peu de chance de se trouver déjà dans le contenu la matrice (ici les cellules de la plage B2:B4). 

= JOINDRE.TEXTE(CAR(29);VRAI;B2:B4)
  • CAR(29) est le caractère "Séparateur de groupe" (caractère non affichable ni imprimable). Les caractères 30 et 31 peuvent aussi être utilisés par exemple.

Trouver le code d'un caractère

La fonction Code

Permet de renvoyer le code décimal du premier caractère ASCII d'un texte. Le code renvoyé correspond au jeu de caractères utilisé par l'ordinateur.

= CODE(Texte)
  • Texte : Chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs numérique(s) correspondante(s) au(x) 1er caractère(s) de la/les chaînes désigné(s) par Texte.

Exemple d'utilisation

= CODE("AZ") = 65 (code du "A")

La fonction UNICODE

Permet de renvoyer le code décimal du premier caractère UNICODE d'un texte.

= UNICODE(Texte)
  • Texte : Chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs numérique(s) correspondante(s) au(x) 1er caractère(s) de la/les chaînes désigné(s) par Texte.

Exemples d'utilisation

Exemple 1 :

= UNICODE("☯") = 9775 (soit 262F en hexadécimale)

Exemple 2 : En recherche d'erreur, pour contrôler les caractères d'un texte.

= LET(txt;H3;
     m;MAP(SEQUENCE(;NBCAR(txt));LAMBDA(i;STXT(txt;i;1)));
     ASSEMB.V(m;UNICODE(m)))

Résultat :

Tableau résultats de la formule

On remarque ici la présence d'une tabulation (code 9) après l'espace (code 32), invisible dans la cellule mais impactant un décompte, un remplacement, une extraction de caractères par exemple.

Compter le nombre de caractère

La fonction NBCAR

Permet de dénombrer les caractères d'une chaîne.

= NBCAR(Texte)
  • Texte : Chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs numérique(s) correspondante(s) au nombre de caractères de la/les chaînes désigné(s) par Texte.

Exemples d'utilisation

= NBCAR("1Forme") => 6

= NBCAR(124)      => 3

D'autre exemples d'utilisation de NBCAR sont présentés dans les exemples d'utilisation des autres fonctions dans la suite de l'article.

Ce décompte peut-être/sembler faux dans certains cas ! Voir Nouveaux paramètres dans les options de calculs.

Formater une chaîne

Changer la casse

Les fonctions MAJUSCULE et MINUSCULE

Permet de passer de lettres capitales (MAJUSCULES) à lettres Minuscules et inversement.

= MAJUSCULE(Texte)

= MINUSCULE(Texte)
  • Texte : Chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) texte désigné(s) par Texte.

Exemples d'utilisation
= MAJUSCULE("Le théâtre") => LE THÉÂTRE

= MINUSCULE("LE THÉÂTRE") => le théâtre

Remarque :

Pour comparer 2 chaînes de texte, sans prendre en compte la différence liée à la présence d'accent, une astuce de programmation est de passer le texte en majuscule. Cette astuce n'est pas utilisable avec la fonction MAJUSCULE qui conserve les accents.

Formule pour supprimer les accents
=LET(ch;B2;
maa;"àâéèêëîôöùûüÀÂÉÈÊËÎÔÖÙÛÜ";
msa;"aaeeeeioouuuAAEEEEIOOUUU";
REDUCE(ch;SEQUENCE(NBCAR(maa));LAMBDA(c;i;SUBSTITUE(c;STXT(maa;i;1);STXT(msa;i;1)))))
  • Ch : Chaîne à modifier (Déclaration inutile mais faite par soucis de simplification d'utilisation, je préfère ne pas devoir aller modifier des références variables parsemées dans le "corps' de la formule mais simplement au début de celle-çi).
  • maa : Matrice des caractères Avec Accents devant être remplacées (liste non exhaustive).
  • msa : Matrice des caractères Sans Accents servant de substituts aux caractères correspondants de maa.

Remarque :

  • Un utilisation "hors normes" de la fonction REDUCE (sur une chaîne de texte et avec un "cumul" qui n'en est pas un).
  • Cette formule peut être facilement adaptée pour crypter/décrypter un texte par substitution 😁.

Formule d'inversion de la casse

=LET(ch;"Le Théâtre";
     CONCAT(MAP(STXT(ch;SEQUENCE(NBCAR(ch));1);LAMBDA(ca;SI(UNICODE(ca)>96;MAJUSCULE(ca);MINUSCULE(ca))))))
     
     => lE tHÉÂTRE

La fonction NOMPROPRE

Mets les 1ères lettres de chaque mot en MAJUSCULE et le reste en minuscule.

= NOMPROPRE(Texte)
  • Texte : Chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) texte désigné(s) par Texte où chaque 1ère lettre de chaque mot est en Majuscule et le reste en Minuscule.

Exemple d'utilisation
= NOMPROPRE("Le théâtRE de dijon") => "Le Théâtre De Dijon"

Conversion alphanumérique/numérique

Renvoyer une chaîne après application d'un format d'affichage

La fonction DEVISE

Permet de modifier une valeur (nombre ou chaîne constituée uniquement de chiffres) de manière à correspondre à l'affichage généré par le format de nombre Monétaire que l'on a l'habitude d'appliquer aux cellules d'Excel.

Rappel :

  • Le rendu du format Monétaire dépend de la configuration
  • Ici aussi on est limité à 15 chiffres significatifs.
= DEVISE( Nombre ; [Décimales])
  • Nombre : Une valeur numérique ou une chaîne représentant un nombre (unique ou matrice de valeurs).
  • Décimales : (facultatif) Le nombre de décimales (unique ou matrice de valeurs) à afficher (un arrondi sera réalisé).
    • Par défaut 2 chiffres.
    • Si négatif : -1 arrondi à la dizaine, -2 arrondi à la centaine...

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) texte désigné(s) par Texte.

Exemples d'utilisation
= DEVISE(2500)    => 2 500,00 €
= DEVISE("2500")  => 2 500,00 €

= DEVISE(2500 ; 3)    =>  2 500,000 €
= DEVISE(12345 ; -2)  => 12 300 €

Remarque : Pour simplifier, j'utilise plutôt la fonction TEXTE.

La fonction TEXTE

Permet de modifier une valeur (nombre ou chaîne constituée uniquement de chiffres) de manière à correspondre aux affichages générés par les formats de nombre que l'on a l'habitude d'appliquer aux cellules d'Excel.

Rappel : ici aussi on est limité à 15 chiffres significatifs.

= TEXTE(Valeur ; Format_text)
  • Valeur : Une valeur numérique ou une chaîne représentant un nombre (unique ou matrice de valeurs).
  • Format_text : Chaine (unique ou matrice de valeurs) symbolisant le format d'affichage à appliquer à Valeur (Voir Téléchargements liés à Excel > Codes de formatage).

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) texte désigné(s) par Texte.

Exemples d'utilisation
= TEXTE("2500";"# ##0,00 €")       => 2 500,00 €  (correspond à DEVISE)
= TEXTE( 2500 ;"# ##0,00 €")       => 2 500,00 €  (correspond à DEVISE)
= TEXTE( 2500 ;"# ##0,00 "" K€""") =>     2,5  K€ 
= TEXTE( 0,45 ;"0,00%")            =>    45,00%
= TEXTE( 2,25 ;"?/4")              => 9/4 (Valeur limité à 2^13 si /4, 2^12 si /8...)     
= TEXTE(12800 ;"0,00E+00")         =>     1,28E+04
= TEXTE( 51,8 ;"00000")            => 00052

= TEXTE(A2;"jjjj jj mmmm aaaa hh:mm:ss") => mardi 18 mars 2025 08:40:00

= TEXTE(A2;"""Vous devez ""# ##0,00 €;""Avoir de ""# ##0,00 €;""Soldé"";""Erreur""")
   si A2 = 1000  => Vous devez 1 000,00 €
   si A2 = -500  => Avoir de 500,00 €
   si A2 = 0     => Soldé
   si A2 = "abc" => Erreur
  
= TEXTE(A2;"[<2]0"" pièce"";0"" pièces """)
   si A2 = 1 => 1 pièce
   si A2 = 5 => 5 pièces

La fonction CTXT

Permet de modifier une valeur (nombre ou chaîne constituée uniquement de chiffres) de manière à correspondre à l'affichage généré par le format de nombre Nombre (Choix du nombre de décimale et présence de séparateur de milliers).

= CTXT(Nombre ; [Décimales] ; [No_séparateur])
  • Nombre : Une valeur numérique ou une chaîne représentant un nombre (unique ou matrice de valeurs).
  • Décimales : Valeur numérique entière (unique ou matrice de valeurs) spécifiant le nombre de décimale à afficher (= aussi à la position de l'arrondi).
    • Par défaut 2 chiffres.
    • Valeur négative : -1 arrondi à la dizaine, -2 arrondi à la centaine...
  • No_séparateur : (FAUX par défaut) NON présence des séparateurs de milliers.

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) texte désigné(s) par Texte.

Exemples d'utilisation
= CTXT("4250,259")     => 4 250,26
= CTXT(4250,259)       => 4 250,26
= CTXT(4250,259;1;VRAI)=>  4250,3
= CTXT(4250,259;-2)    => 4 300

Remarque : Pour simplifier, j'utilise plutôt la fonction TEXTE.

Renvoyer une chaîne sans format d'affichage spécifique

La fonction VALEUR.EN.TEXTE

Renvoie les valeurs de texte inchangées et convertit les valeurs non textuelles, sans leur format d'affichage, en texte.

= VALEUR.EN.TEXTE(Valeur; [Format])
  • Valeur : Une valeur numérique ou une chaîne représentant un nombre (unique ou matrice de valeurs).
  • Format :
    • 0 : Valeur par défaut. Valeurs sans formatage en alphanumérique.
    • 1 : Si Valeur était une chaîne de texte, cette valeur sera renvoyée entourée de guillemets.

Résultat : Valeur ou matrice de valeurs alphanumériques (textes) correspondante(s) au(x) valeurs désigné(s) par Texte convertit en alphanumérique.

N'ayant pas jamais eu le besoin d'obtenir un résultat correspondant au paramètre Format 1, j'utilise à la place de cette fonction cette formule :

= A2 & ""

Donnant un résultat identique au paramètre Format 0.

La fonction TABLEAU.EN.TEXTE

Génère une chaîne à partir d'une plage. La chaîne est constituée de la concaténation des valeurs des cellules (Ligne par ligne) avec un séparateur. L'inverse de la fonction FRACTIONNER.TEXTE.

= TABLEAU.EN.TEXTE(Matrice ; Format)
  • Matrice : Plage de cellules.
  • Format
    • 0 : Valeur par défaut, le séparateur sera le point-virgule.
    • 1 : Le séparateur point marquera un changement de colonne, le séparateur point-virgule marquera un changement de ligne. Les portions constituées de chaîne de texte seront entre guillemets. On obtiendra ainsi une chaîne correspondant à une constante matricielle.

Résultat : Une chaîne de texte, au maximum de 32767 caractères (limite de cellule).

Exemples d'utilisation

Tableau de départ

A 2%
1 520,00 €
VRAI
16/03/2025

= TABLEAU.EN.TEXTE(Tableau)    => A; 0,02; 1520; ; ; VRAI; 45732; 
= TABLEAU.EN.TEXTE(Tableau;1)  => {"A".0,02;1520.;.VRAI;45732.}

Renvoyer une valeur numérique

L'article suivant propose plusieurs autres solutions de conversion : Trucs et astuces pour convertir les types de valeurs.

La fonction CNUM

Convertit en nombre des chaînes de caractères représentant un nombre.

= CNUM(Texte)
  • Texte : Une chaîne représentant un nombre (unique ou matrice de valeurs).

Résultat : Valeur ou matrice de valeurs numérique correspondante(s) au(x) texte désigné(s) par Texte.

Exemples d'utilisation
= CNUM("12") => 12

= CNUM("Deux") => #Valeur!

Cette fonction est souvent inutile, les fonctions d'Excel générant nativement la conversion le plus souvent.

Exemple : Déterminer le sexe d'une personne en fonction d'un ANCIEN n° de sécurité sociale français (1er chiffre du n° pouvant aller de 0 à 9).

= SI(EST.PAIR(GAUCHE(NumSS));"F";"H")
= SI(MOD(GAUCHE(NumSS);2);"H";"F")
  • L'extraction (fonction GAUCHE, par défaut d'un caractère) renvoie une valeur texte qui est convertit directement par les fonctions EST.PAIR et MOD.

La fonction VALEURNOMBRE

Convertit un texte en nombre en fonction de paramètres régionaux (séparateur décimale, séparateur de groupe).

= VALEURNOMBRE(Texte ; [Séparateur_décimal] ; [Séparateur_groupe])
  • Texte : Une chaîne représentant un nombre (unique ou matrice de valeurs).
  • Séparateur_décimal : Le caractère utilisé dans Texte comme séparateur décimal (si omis ou non trouvé, celui des paramètres Windows).
  • Séparateur_groupe : Le caractère utilisé dans Texte comme séparateur de groupe/classe (si omis ou non trouvé, celui des paramètres Windows).
Exemple d'utilisation

Nos amis les Américains écrivent parfois les nombres ainsi : 1,234,567.89 (virgule en tant que séparateur de milliers et point comme séparateur décimal).
Cette syntaxe est à l'inverse de notre façon de faire (1.234.567,89) !

Voici comment pallier ce problème si on a récupéré ce type de valeur.

= VALEURNOMBRE("1,234,567.89";".";",") => 1234567,89

Autre solution
= SUBSTITUE(SUBSTITUE("1,234,567.89";",";"");".";",")*1 => 1234567,89

Nettoyer une chaîne

La fonction SUPPRESPACE

Supprime les espaces inutiles (espaces dédoublés, espaces avant le texte, espaces après le texte).

= SUPPRESPACE(Texte)
  • Texte : Une chaîne (unique ou matrice de valeurs).

Exemples d'utilisation

= SUPPRESPACE(" 9    lettres   ")             => "9 lettres"
= SUPPRESPACE("A" & car(160) & car(160)& "B") => "A  B" (l´espace insécable (code 160) n´est pas concerné)

Remarque :

Pour supprimer tous les espaces on utilisera la fonction SUBSTITUE.

=SUBSTITUE(" 9    lettres   ";" ";"") => "9lettres"

La fonction EPURAGE

Supprime du texte les caractères de contrôle (caractères invisibles, non imprimables du code ASCII 0 à 31) mais parfois gênant.
Problème que l'on rencontre parfois avec du texte importé d’autres applications.

= EPURAGE(Texte)
  • Texte : Une chaîne (unique ou matrice de valeurs).

Exemples d'utilisation

= NBCAR(        CAR(160)&"une" & CAR(9)&" tabulation ")  => 17
= NBCAR(EPURAGE(CAR(160)&"une" & CAR(9)&" tabulation ")) => 16 (seul le caractère CAR(9), la tabulation, a été supprimé)

Filtrer les valeurs de type chaînes

La fonction T

Permet de ne renvoyer que les chaînes d'un tableau de valeurs.

= T(Valeur)
  • Valeur : Valeur unique à filtrer.

Résultat : Matrice où les valeurs numérique (dont Dates) et booléennes sont remplacées par une chaîne vide.

Exemples d'utilisation

= T("ABC") => "ABC"
= T("023") => "023"
= T(123)   => ""
= T(VRAI)  => ""

Autre solution renvoyant une matrice des chaîne présente

= FILTRE(Plage;ESTTEXTE(Plage))

Merci pour votre attention bienveillante.

Article intéressant ?

Cliquez sur une étoile pour noter cet article !

Note moyenne 0 / 5. Nombre de votes : 0

Aucun vote pour l'instant ! Soyez le premier à noter ce post.

Nous sommes désolés que cet article ne vous ait pas été utile !

Améliorons cet article !

Dites nous comment nous pouvons améliorer cet article ?

Publications similaires

Les nouvelles fonctions GROUPER.PAR (GROUPEBY) et PIVOTER.PAR (PIVOTBY)

ParArnaud

Découvrez les fonctions GROUPER.PAR et PIVOTER.PAR(CROISER.AVEC). Nous permettant de faire du croisement/agrégation de données comme via les tableaux croisés dynamiques mais avec plus de liberté dans le choix de l’opération d’agrégation et une mise à jour dynamique.

Laisser un commentaire

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