0
(0)
Sommaire

Dans l'article nous avons vu les fonctions suivantes :

  • CAR et UNICAR.
  • CODE et UNICODE.
  • NBCAR.
  • MAJUSCULE, MINUSCULE et NOMPROPRE.
  • CNUM et CTXT.
  • SUPPRESPACE et EPURAGE.
  • DEVISE et TEXTE.
  • VALEUR.EN.TEXTE, VALEURNOMBRE.
  • T.

On continue la revue des fonctions de cette famille.

Chercher dans un texte

Les fonctions CHERCHE et TROUVE

Déterminer la position d'une chaîne dans une autre chaîne (Inversement à CHERCHE, TROUVE différentie les MAJUSCULES des minuscules dans sa recherche).

= CHERCHE(Texte_cherché ; Texte ; [No_départ])
=  TROUVE(Texte_cherché ; Texte ; [No_départ])
  • Texte_cherché : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) correspondante(s) à ce que l'on recherche dans Texte.
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on va effectuer la recherche de Texte_cherché.
  • No_départ : Valeur numérique (unique ou matrice de valeurs) correspondante(s) à la position où commencera la recherche (1 par défaut).

Résultat : Valeur numérique (unique ou matrice de valeurs) correspondant à la position de Texte_cherché dans Texte. Si Texte_cherché est non trouvé, on obtient #VALEUR!

Exemples d'utilisation

= CHERCHE(3;83039)   => 2
= CHERCHE(3;83539;3) => 4
= CHERCHE("h";"RecHerche") => 4
=  TROUVE("h";"RecHerche") => 8

Trouver la position du 2ème tiret dans une référence (formule souvent imbriquée dans une fonction STXT).

B2 : AA-123-BBBB (Le nombre de caractères entre les tirets n´est pas fixe) 

=CHERCHE("-";B2;CHERCHE("-";B2)+1) => 7

On cherche un tiret après la position du 1er tiret.

La fonction REGEX.TEST

Voir l'article Des fonctions utilisant les expressions régulières !

Modifier une portion d'un texte

La fonction SUBSTITUE

Remplacer des portions d'une chaîne de texte correspondantes aux résultats d'une recherche par une autre portion de chaîne (Attention : sensible à la casse).

= SUBSTITUE(Texte ; Ancien_texte ; Nouveau_texte ; [No_position])
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on va effectuer la recherche d'Ancien_texte.
  • Ancien_texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) correspondante(s) à ce que l'on recherche dans Texte.
  • Nouveau_texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui remplacera Ancien_texte dans Texte.
  • No_position : Valeur numérique (unique ou matrice de valeurs) correspondante(s) à l'occurrence d'Ancien_texte où le remplacement par Nouveau_texte sera fait). Si omis, toutes les occurrences seront remplacées.

Résultat : Chaîne de texte (unique ou matrice de valeurs) correspondante à la valeur de Texte modifiée. Si Ancien_texte n'est pas trouvé on récupère la valeur non modifiée de Texte.

Exemples d'utilisation

=SUBSTITUE("Il se gare à la gare sans crier gare et prend un cigare";"gare";"station")
   => "Il se station à la station sans crier station et prend un cistation"
   Toutes les occurrences de "gare" sont remplacées par "station".

=SUBSTITUE("Il se gare à la gare sans crier gare et prend un cigare";" gare ";" station ")
   => "Il se station à la station sans crier station et prend un cigare"
  Toutes les occurrences de " gare " sont remplacées par " station " sauf cigare (remarquer les espaces)
    
 =SUBSTITUE("Il se gare à la gare sans crier gare et prend un cigare";"gare";"station";2)
   => "Il se gare à la station sans crier gare et prend un cigare"
   Seule la 2ème occurence est remplacée.

= SUBSTITUE(100004;"00";"0") => "1004" et non 104 (une seul "passe" pour la recherche)

= SUBSTITUE("10.03.2025";".";"/")*1 => 10/03/2025 (Valeur à formater en date)

= SUBSTITUE("Adresse ip, IP, iP, ip";"ip";"Url") => "Adresse Url, IP, iP, Url" (sensibilité à la casse)

La fonction REMPLACER

Remplacer une portion d'une chaîne de caractères par une autre chaîne à une position précise.

= REMPLACER(Ancien_texte ; No_départ ; No_car ; Nouveau_texte)
  • Ancien_texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on souhaite remplacer un nombre donné de caractères.
  • No_départ : Valeur numérique (unique ou matrice de valeurs) spécifiant la position du caractère de Ancien_texte où le remplacement va débuter.
    • Si elle est supérieure au nombre de caractères d'Ancien_texte alors c'est le nombre de caractères d'Ancien_texte qui sera utilisé.
  • No_car : Valeur numérique (unique ou matrice de valeurs) spécifiant le nombre de caractères d’Ancien_texte que Nouveau_texte doit remplacer.
  • Nouveau_texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) spécifiant le(s) texte(s) qui doi(ven)t remplacer les caractères d’Ancien_texte.

Résultat : Chaîne de texte (unique ou matrice de valeurs) correspondante à la valeur d'Ancien_texte modifiée.

Exemples d'utilisation

= REMPLACER("Somme Lyon";7;8;"Dijon")      => "Somme Dijon"  (8 caractères mais 4 auraient suffi)
= REMPLACER("Somme Marseille";7;8;"Dijon") => "Somme Dijone" (8 caractères n´était pas suffisant ici)
= REMPLACER(12345;3;2;78)                  => "12785"

Remarques :

  • No_départ et No_car peuvent être/sembler faux dans certains cas ! Voir Nouveaux paramètres dans les options de calculs.
  • La fonction REMPLACERB est pour les langues asiatiques, pour prendre en charge les jeux de caractères codés sur deux octets et n'est fonctionnelle que si cette langue est définie par défaut.

La fonction REG.REMPLACER

Voir l'article Des fonctions utilisant les expressions régulières !

Découper/Extraire une portion du texte

Les fonctions GAUCHE et DROITE

Extrait le début ou la fin d'une chaîne.

= GAUCHE(Texte ; [No_car])
= DROITE(Texte ; [No_car])
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on va effectuer l'extraction.
  • No_car : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) spécifiant le nombre de caractères à extraire.

Résultat : Chaîne de texte (unique ou matrice de valeurs) correspondante à la portion désignée.

Exemples d'utilisation

= GAUCHE(987654;3)         => "987"
= DROITE("Somme Dijon"; 5) => "Dijon"
= DROITE(GAUCHE("Facture 0020 du 1/1/25";12);4) => "0020"

= GAUCHE("Le 🧑‍🚀 est Grec";4) => "Le 🧑" (Voir Remarques)
= GAUCHE("Le 🧑‍🚀 est Grec";6) => "Le 🧑‍🚀"

Remarques :

  • No_car peut être/sembler faux dans certains cas ! Voir Nouveaux paramètres dans les options de calculs.
  • Les fonctions GAUCHEB et DROITEB sont pour les langues asiatiques, pour prendre en charge les jeux de caractères codés sur deux octets et ne sont fonctionnelles que si cette langue est définie par défaut.

La fonction STXT

Renvoie une portion d'une chaîne de texte à partir d'une position et d'un nombre de caractères à extraire.

= STXT(Texte ; No_départ ; No_car)
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on souhaite faire cette extraction.
  • No_départ : Valeur numérique (unique ou matrice de valeurs) spécifiant la position du caractère de Texte où l'extraction va débuter.
    • Si elle est supérieure au nombre de caractères de Texte alors on obtient une chaîne vide (on utilise souvent comme valeur 9^9 pour prendre tous les caractères suivant).
  • No_car : Nombre maximale de caractères à renvoyer (dépasser le nombre de caractères possible ne génère pas d'erreur).

Résultat : Chaîne de texte (unique ou matrice de valeurs) correspondante à la portion désignée.

Exemples d'utilisation

= STXT(987654;1;3)                   => "987"   (<=> GAUCHE(987654;3))

= STXT("Facture 0020 du 1/1/25";9;4) => "0020"  (<=>DROITE(GAUCHE("Facture 0020 du 1/1/25";12);4))

= STXT("Somme Dijon";7;9^9)          => "Dijon" (<=> DROITE("Somme Dijon";5)
                                                 ! pas adapté pour une autre ville)
= STXT("Excel";10;2)                 => ""      (Chaîne vide)

Extraire la partie centrale d'une référence constituée de 3 parties de taille variable.

Valeur en B2 : AA-123-BBBB 

= LET(t;B2;
      pt_1;CHERCHE("-";t);
      pt_2;CHERCHE("-";t;pt_1+1);
      STXT(t;pt_1+1;pt_2-pt_1-1)
)                                => "123"

Les fonctions TEXTE.APRES et TEXTE.AVANT

Renvoie une portion d'une chaîne de texte se trouvant après/avant un caractère spécifique (Fonctions récentes).

= TEXTE.APRES(Texte ; Délimiteur ; [Instance_num] ; [Match_mode] ; [Match_end] ; [Si_non_trouvé])
= TEXTE.AVANT(Texte ; Délimiteur ; [Instance_num] ; [Match_mode] ; [Match_end] ; [Si_non_trouvé])
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) dans laquelle on souhaite faire cette extraction
  • Délimiteur : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera/seront recherchée(s) dans Texte et spécifiant ainsi la position après/avant laquelle sera faite l'extraction.
  • Instance_num : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) correspondante à l'instance du délimiteur après/avant laquelle l'extraction du texte se fera.
    • La valeur par défaut est 1.
    • Une valeur négative imposera une rechercher à rebours (part de la fin du texte vers le début).
  • Match_mode : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs).
    • 0 ou omis : Sensible à la casse (différenciation MAJUSCULE-minuscule).
    • 1 : Ignore la casse.
  • Match_end : Paramétrage lorsque la recherche de Délimiteur atteindre la fin du texte. Cela permet d'éviter le résultat #N/A quand la recherche de Délimiteur est infructueuse, on obtient une chaîne vide avec Instance_num>0 et la chaîne de Texte avec Instance_num<0.
    • 0 : Valeur par défaut, on obtient #N/A quand la recherche de Délimiteur est infructueuse.
    • 1 : Considère qu'une occurrence de Délimiteur est trouvée à la fin de la recherche.
      • À droite du dernier caractère de Texte en sens normale de recherche (Instance_num>0).
      • À gauche du 1er caractère de Texte si recherche à rebours (Instance_num<0).
  • Si_non_trouvé : Valeur renvoyée si aucune correspondance n’est trouvée. Par défaut, #N/A est renvoyé.

Résultat : Chaîne de texte (unique ou matrice de valeurs) correspondante à la portion désignée.

Exemples d'utilisation

= TEXTE.AVANT("Le 1er mot";" ")             => "Le"
= TEXTE.APRES("Monsieur Jean Dupont";" ")   => "Jean Dupont"
= TEXTE.APRES("Le dernier mot"; " ";-1)     => "mot"

=TEXTE.APRES("abcde";"@")                   => #N/A!
=TEXTE.APRES("abcde";"@";;;;"Pas trouvé")   => "Pas trouvé"

=TEXTE.APRES("abcde";"@";;;1;"Pas trouvé")  => ""   (l´argument Si_non_trouvé est inutile dans ce cas)
=TEXTE.APRES("abcde";"@";-1;;1;"Pas trouvé")=> "abcde" (l´argument Si_non_trouvé est inutile dans ce cas)

B2 : "Dupont ; Jean"
= TEXTE.APRES(B2;{" ; ";", "}) => "Jean"

B2 : "Martin, Luc"
= TEXTE.APRES(B2;{" ; ";", "}) => "Luc" (la même formule fonctionne dans les 2 cas)

La fonction FRACTIONNER.TEXTE

Découpe une chaîne en plusieurs portions au niveau des caractères mentionnés comme délimiteurs dans la formule (Fonction récente).

= FRACTIONNER.TEXTE(Texte ; Col_delimiter ; [Row_delimiter] ; [Ignore_empty] ; [Match_mode] ; [Pad_with])
  • Texte : Texte à fractionner.
  • Col_delimiter : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera/seront recherchée(s) dans Texte et spécifiant ainsi un changement de colonne dans la matrice résultat.
  • Row_delimiter : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera/seront recherchée(s) dans Texte et spécifiant ainsi un changement de ligne dans la matrice résultat.
  • Ignore_empty : Valeur booléenne permettant d'ignorer ou non les délimiteurs consécutifs. La valeur par défaut est FALSE ce qui créera des cellules vides en cas de délimiteurs consécutifs.
  • Match_mode : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs).
    • 0 ou omis : Sensible à la casse (différenciation MAJUSCULE-minuscule).
    • 1 : Ignore la casse.
  • Pad_with : Valeur unique permettant de remplacer les valeurs #N/A ajoutées par défaut pour combler les "trous" (valeurs non présentes dans Texte) dans la matrice résultat.

Résultat : Une matrice de valeurs.

Exemples d'utilisation

Cas avec de délimiteurs consécutif
= FRACTIONNER.TEXTE("Produit,Qt>A,10>>C,20" ; "," ; ">" ; VRAI)

Résultat :

ProduitQt
A10
C20

Pas de lignes vide générée entre la ligne A et C malgré les deux ">".

Cas avec une constante matricielle pour Col_delimiter.
=FRACTIONNER.TEXTE("Jean, Paul ; Marc,, Luc/Marie;,Sophie";{",";";"};"/";;;"???")

Résultat :

Jean PaulMarc Luc
MarieSophie??????

Cas d'utilisation pour extraire une partie précise (évite ici l'utilisation de fonctions CHERCHE imbriquées).
= INDEX(FRACTIONNER.TEXTE("Facture 0020 du 1/1/25";" ");2) => "0020"

La fonction REGEX.EXTRAIRE

Voir l'article Des fonctions utilisant les expressions régulières !

Créer une chaîne de texte

La fonction CONCAT

Combine le texte de plusieurs valeurs (plages, chaînes, matrices).

= CONCAT(Texte1 ; [Texte2]; ...)
  • Texte1 à Texte254 : Valeurs à associer.

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

Exemples d'utilisation

B2="AB", B3=14, B4=5%

= CONCAT(B2:B4) => "AB140,05"
= CONCAT("Somme = " ; B3) => "Somme = 14"

La fonction JOINDRE.TEXTE

Combine du texte à partir de plusieurs plages et/ou chaînes et inclut un séparateur entre chaque portion de texte à combiner (Fonction récente).

= JOINDRE.TEXTE(Délimiteur ; Ignorer_vide ; Texte1 ; [Texte2] ; …)
  • Délimiteur : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera/seront insérée(s) entre chaque élément combiné.
  • Ignorer_vide : Valeur booléenne permettant d'ignorer ou non les valeurs vides. La valeur par défaut est TRUE, ce qui évitera d'ajouter des délimiteurs inutiles.
  • Texte1 à Texte252 : Valeurs à associer.

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

Exemples d'utilisation

= JOINDRE.TEXTE("-";;"Jean";"";"Paul")                           => "Jean-Paul"
= JOINDRE.TEXTE("-";FAUX;"Jean";"";"Paul")                       => "Jean--Paul"
= JOINDRE.TEXTE("";;"Jean";"Paul")                               => "JeanPaul"
= JOINDRE.TEXTE({"-";"="};;"Jean";"Paul";"Luc";"Marie";"Pierre") => "Jean-Paul=Luc-Marie=Pierre"

Utiliser du texte

Les fonctions DÉTECTERLANGUE et TRADUIRE

Voir l'article Les nouvelles fonctions TRADUIRE et DÉTECTERLANGUE

La fonction REPT

Répète un texte un certain nombre de fois.

= REPT(Texte ; No_fois)
  • Texte : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera répétée.
  • No_fois : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) indiquant le nombre de répétition.

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

Exemples d'utilisation

Pour simuler une barre de progression/un graphique en barre dans des cellules par exemple.

Génération de barres de progression/graphiques en barre avec la fonction REPT

La fonction EXACT

Teste si des paires de valeurs sont identiques (même caractères ayant la même casse). Elle ne tient pas compte des différences de mise en forme.

= EXACT(Texte1 ; Texte2)
  • Texte1 : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs) qui sera comparée à Texte2.
  • Texte2 : Valeur numérique ou chaîne de texte (unique ou matrice de valeurs).

Résultat : Valeur booléenne (unique ou matrice de valeurs) indiquant si les chaîne sont identiques.

Exemple d'utilisation

Elle est employée souvent avec les fonctions utilisant des critères de recherche ou de filtre (NB.SI.ENS, EQUIVX, RECHERCHEX, FILTRE...) afin d'avoir des critères de correspondances plus stricte qui prennent en compte de la casse lors de leurs comparaisons.

= EXACT("Excel";"excel") => FAUX (1ère lettre différente)

Les autres fonctions de cette famille

La fonction BATHTEXT

Pourquoi n'est-elle pas masquée comme d'autre fonctions spécifiques aux langues asiatiques ?

La fonction POURCENTAGE.DE

Pourquoi est-elle dans la liste des fonctions TEXTE (du moins sur mon poste avec cette version), c'est un mystère !

Autres fonctions liées aux chaînes de texte

Les fonctions ESTTEXTE et ESTNONTEXTE

Permet de déterminer si une valeur est du texte (alphanumérique) ou non.

= ESTTEXTE(Valeur)
= ESTNONTEXTE(Valeur)
  • Valeur : Valeur unique ou matrice de valeurs à tester.

Résultat : Valeur unique ou matrice de valeurs booléenne.

Exemples d'utilisation

= ESTTEXTE("Excel") => VRAI
= ESTTEXTE("12")    => VRAI
= ESTTEXTE("")      => VRAI
= ESTTEXTE(12)      => FAUX
= ESTTEXTE(VRAI)    => FAUX
= ESTTEXTE(#N/A!)   => FAUX
= ESTTEXTE(5/0)     => FAUX (#DIV/0!)

Remarque : La fonction ESTNONTEXTE renvoie le résultat inverse de la fonction ESTTEXTE.

La fonction

Résultat :

Exemple d'utilisation

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

Laisser un commentaire

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