Accueil

Gauche
Droite
Stxt
NbCar
Texte
Cnum
Majuscule
Nompropre
Code
Rept
Remplacer
Substitue
Cherche
Trouve
Exact
Formules matricielles sur les chaînes

La concaténation de chaînes se fait avec l'opérateur &. En C1 , la formule =A1&B1 fait
apparaître DUPONTJEAN.

On reconstitue l'adresse email à partir du prénom et du nom.

La formule =SI($B$1>D6;"Retard:" &$B$1-D6 & "jours";"") affiche Retard lorsque la date du jour
est supérieure à la date de paiement.

Les commandes Copier et Collage Spécial/Valeur permettent d’éliminer la formule.

GAUCHE (chaîne;nombre)

Donne les caractères de gauche d'une chaîne. En B1,la formule =GAUCHE(A1;2) fait apparaître 78

DROITE (chaîne;nombre)

  Donne les caractères de droite d'une chaîne.

=DROITE(A1;2) donne 78

STXT (chaîne;début;nombre)

   Donne les caractères du milieu d'une chaîne.

NBCAR (chaîne)

   Donne le nombre de caractères d'une chaîne.

TEXTE(nombre;format_texte)

  Convertit une un nombre en texte.

      =TEXTE(123;"0,00 Francs")  donne 123,00 Francs

CNUM(chaîne)

   Convertit une chaîne en nombre.

MAJUSCULE (chaîne)

Convertit une chaîne en majuscules.

NOMPROPRE (chaîne)

Convertit la première lettre en majuscule et les autres  en minuscules.  

    =NOMPROPRE("CEUZIN")  donne Ceuzin

CAR(code)

 Retourne le caractère associé au code spécifié.

     =CAR(65) donne A

CODE(caractère)

Donne le code d'un caractère.

     =CODE("A") donne 65

REPT(chaîne;nombre)

  Répète une chaîne.

     =REPT("A";4)  donne AAAA

REMPLACER(chaîne;début;nombre;nouvelle_chaîne)

Remplace dans une chaîne à partir de début le nombre de caractères spécifié par nouvelle chaîne.

      =REMPLACER("ABCDEF";2;3;"XYZ")   donne AXYZEF

SUBSTITUE(chaîne;ancienne_chaîne;nouvelle_chaîne;position)

Remplace ancienne_chaîne par nouvelle_chaîne. Si position est spécifié, seul le texte à la position indiquée est remplacé.

      =SUBSTITUE("Dupont";"p";"xy")  donne Duxyont

      =SUBSTITUE("ABC ABC";"B";"X";2) donne ABC AXC

Nombre de virgules

A1 contient aaa,bbb,ccc,ddd: on veut obtenir le nombre de ','

=NBCAR(A1)-NBCAR(SUBSTITUE(A1;",";"")) donne 3

Position de la 3e virgule

A1 contient aaa,bbbbb,ccc,ddd: On veut la position de la 3e virgule

=CHERCHE("|";SUBSTITUE(A1;",";"|";3))

Chaîne après le dernier \

A1 contient Opérations\Anomalie horaire LIN\Retard.

On veut récupérer la chaine après le dernier \ (retard)

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))+1;999)

ou

=DROITE(A1;EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
Valider avec maj+ctrl+entrée

Extraction des items d'une chaîne

Chaîne en A1: aaa bbb ccc dddd eeee ffff

Position des espaces dans la chaîne

Substitue

Premier item (aaa):
=GAUCHE(A1;TROUVE(" ";A1))

Deuxième item (bbb):
=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";2)));TROUVE(" ";A1)+1;99)

Troisième item (ccc):
=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";3)));CHERCHE("|";SUBSTITUE(A1;" ";"|";2))+1;99)

Dernier item (ffff):
=STXT(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))))+1;99)

ou

=DROITE(A1;EQUIV(" ";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
valider avec maj+ctrl+entrée

Avant dernier item (eeee):
=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-0)));
CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-1))+1;99)

Avant avant dernier item (dddd):
=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-1)));
CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-2))+1;99)

Autre exemple

Pour découper Chat,chien,oiseau,poule,tigre dans plusieurs cellules

Decoupe phrase

En B1: =GAUCHE($A$1;TROUVE(",";$A$1)-1)

En B2:
=SI(LIGNES($1:1)<NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;",";""));
STXT(GAUCHE($A$1;TROUVE("|";SUBSTITUE($A$1;",";"|";LIGNES($1:2)))-1);TROUVE("|";
SUBSTITUE($A$1;",";"|";LIGNES($1:1)))+1;99);
SI(LIGNES($1:1)=NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;",";""));
STXT($A$1;TROUVE("|";SUBSTITUE($A$1;",";"|";NBCAR($A$1)-NBCAR(SUBSTITUE($A$1;",";""))))+1;99);""))

Extraction des chaînes de chaine1\chaine2\chaine3\chaine4

ExtraitChaines

Chaîne avant le dernier \
=GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))-1)

ou

=GAUCHE(A1;NBCAR(A1)-EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0)-1)
valider avec maj+ctrl+enttrée

Chaîne après le dernier \
=STXT(A1;CHERCHE("|";SUBSTITUE(A1;"\";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"\";""))))+1;99)

ou

=DROITE(A1;EQUIV("\";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
Valider avec maj+ctrl+entrée

CHERCHE (chaîne_cherchée;chaîne;début)

Donne la position d'une sous_chaîne dans une chaîne à partir de début. Majuscules/Minuscules sont confondues.

=CHERCHE("n";"Dupont")  donne 5
=CHERCHE("N";"Dupont")  donne 5

=CHERCHE("p?n";"Dupant") -->3
=CHERCHE("t*e";"Boisgonthier") --> 8

TROUVE(chaîne_cherchée;chaîne;début)

Donne la position d'une sous_chaîne dans une chaîne à partir de début. Majuscules/Minuscules ne sont pas confondues.

     =TROUVE("p";"Dupont")  donne 3

Pour extraire la chaîne bbb entre parenthèses de aaaaa(bbb)ccc en A1

=STXT(GAUCHE(A1;TROUVE(")";A1)-1);TROUVE("(";GAUCHE(A1;TROUVE(")";A1)-1))+1;999)

EXACT(chaîne1;chaîne2)

Donne VRAI si chaine1=chaine2.

Tester si la chaîne en B2 est en majuscule

=SI(EXACT(MAJUSCULE(B2);B2);"Majusc";"Non")

Fomules matricielles sur les chaînes

Recherche par la droite dans une chaîne de caractères

On recherche la position du dernier caractère ',' pour récupérer la dernière partie 37.35
En A1: HABT,1025,G1590,TRAM,37.35

=DROITE(A1;EQUIV(",";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
-Valider avec Maj+ctrl+entrée

ou

=DROITE(A1;EQUIV(",";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))

On veut récupérer la dernière partie (Ecran standard)

A1: /Informatique/Périphériques de sortie/Moniteur/Ecran standard

=DROITE(A1;EQUIV("/";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0))
-Valider avec Maj+ctrl+entrée

A1: /Informatique/Périphériques de sortie/Moniteur/Ecran standard/

=SUBSTITUE(DROITE(A1;EQUIV("/";STXT(A1;NBCAR(A1)-LIGNE($1:$255);1);0));"/";"")
-Valider avec Maj+ctrl+entrée

ou

=SUBSTITUE(STXT(A1;CHERCHE("|";SUBSTITUE(A1;"/";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;"/";""))-1))+1;99);"/";"")

On veut récupérer 8,741.59

H 0000000190364654 Internal Message JEAN JEAN 8,741.59 Settled
H 0000000190146254 Internal Message JEAN JEAN 4,644,824.24 Settled

=DROITE(SUBSTITUE(A1;" Settled";"");EQUIV(" ";STXT(SUBSTITUE(A1;" Settled";"");
NBCAR(SUBSTITUE(A1;" Settled";""))-LIGNE($1:$255);1);0))
Valider avec Maj+ctrl+entrée

On veut la dernière date de C2 si B2 est barré

=SI(NON(estbarré(B2));"";SI(ESTERREUR(CHERCHE(",";C2));C2;DROITE(C2;EQUIV(",";
STXT(C2;NBCAR(C2)-LIGNE($1:$255);1);0)) ))
Valider avec Maj+ctrl+entrée

Dans un module (Alt+F11 puis Insertion/Module)

Function EstBarré(c)
  Application.Volatile
  EstBarré = c.Font.Strikethrough
End Function

Récupération du nom et du prénom

=GAUCHE(A4;EQUIV(VRAI;EXACT(STXT(A4;LIGNE($1:$255);3);MAJUSCULE(STXT(A4;LIGNE($1:$255);3)));0))
-Valider avec Maj+ctrl+entrée

=STXT(A4;EQUIV(VRAI;EXACT(STXT(A4;LIGNE($1:$255);3);MAJUSCULE(STXT(A4;LIGNE($1:$255);3)));0)+1;999)
-Valider avec Maj+ctrl+entrée

NomPrénom

Si les noms et prénoms sont dans un ordre quelconque

NomPrénom2

En B7:=SI(CODE(STXT(A7;2;1))>=97;
GAUCHE(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0));
STXT(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999))
Valider avec maj+ctrl+entrée

En C7:=SI(CODE(STXT(A7;2;1))>=97;
STXT(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999);
GAUCHE(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)))
Valider avec maj+ctrl+entrée

Mise en ordre nom/prénom

Dans la liste originale, les noms/prénoms sont dans un ordre quelconque.

En B7:=SI(CODE(STXT(A7;2;1))>=97;A7;
STXT(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999)&" "&
GAUCHE(A7;EQUIV(FAUX;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)))
Valider avec maj+ctrl+entrée

En C7:=SI(CODE(STXT(A7;2;1))>=97;
STXT(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0)+1;999)&" "&
GAUCHE(A7;EQUIV(VRAI;EXACT(STXT(A7;LIGNE($1:$255);3);MAJUSCULE(STXT(A7;LIGNE($1:$255);3)));0));A7)
Valider avec maj+ctrl+entrée

Découpage d'une adresse

En A1: 28 rue du grand Faubourg 33015 La Rochelle

Découpe adresse
Découpe adresse2

Rue
=GAUCHE(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)-5)
Valider avec Maj+ctrl+entrée

Code postal
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)-4;5)
-Valider avec Maj+ctrl+entrée

Ville
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)+2;99)
-Valider avec Maj+ctrl+entrée

En VBA:

Function CodePostal(chaine)
  p = 1
  CodePostal = ""
  Do While p <= Len(chaine) - 4 And CodePostal = ""
     If Mid(chaine, p, 5) Like "#####" Then CodePostal = Mid(chaine, p, 5) Else p = p + 1
  Loop
End Function

Function Rue(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Rue = ""
    If Mid(chaine, p, 5) Like "#####" Then Rue = Left(chaine, p - 2) Else p = p + 1
  Loop
End Function

Function Ville(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Ville = ""
    If Mid(chaine, p, 5) Like "#####" Then Ville = Mid(chaine, p + 6) Else p = p + 1
  Loop
End Function

Donne la partie numérique d'une chaîne (1 seule occurrence)

A1 contient Tph 0130556677 France

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);SOMME((ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1))))*1))
-Valider avec Maj+Ctrl+Entrée

A1 contient Total de Toto : 256.25 xxx. Pour obtenir 256.25

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);
SOMME(--(ESTNUM(CNUM(STXT(SUBSTITUE(A1;".";"0");LIGNE($1:$255);1))))))
-Valider avec Maj+ctrl+entrée

pour obtenir le résultat en numérique avec , (256,25)

=CNUM(SUBSTITUE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);
SOMME(--(ESTNUM(CNUM(STXT(SUBSTITUE(A1;".";"0");LIGNE($1:$255);1))))));".";","))
-Valider avec Maj+ctrl+entrée

A1 contient MR DANIEL 1210 DUPOND

=STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0);4)
-Valider avec Maj+Ctrl+Entrée

Transformation rue

=STXT(A1;EQUIV(FAUX;ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"));0);999)&"("&GAUCHE(A1;EQUIV(FAUX;ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"));0)-1)&")"
Valider avec Maj+Ctrl+Entrée

Nombre de majuscules en A1

=SOMMEPROD(((CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))>=65)*(CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))<=96)))
ou
=NBCAR(A1)-SOMMEPROD(--ESTERREUR(TROUVE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
ou
=NBCAR(A1)-SOMMEPROD(--ESTERREUR(TROUVE(STXT(A1;LIGNE($1:$255);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Elimine le numérique en début de chaîne

=SUPPRESPACE(STXT(A1;EQUIV(FAUX;ESTNUM(--(STXT(A1;LIGNE($1:$255);1)));0);999))
Valider avec Maj+ctrl+entrée

Caractères invalides

On veut connaître le nombre de caractères invalides en B2

=SOMMEPROD(--(ESTERREUR(CHERCHE(STXT(B2;LIGNE($1:$255);1);A2))))

Séparation numérique

=GAUCHE(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)-2)
Valider avec Maj+ctrl+entrée

=STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);99)
Valider avec Maj+ctrl+entrée

Séparation majuscule

=STXT(A1;1;EQUIV(VRAI;EXACT(STXT(A1;LIGNE($2:$255);1);MAJUSCULE(STXT(A1;LIGNE($2:$255);1)));0))
valider avec maj+ctrl+entrée

=STXT(A1;EQUIV(VRAI;EXACT(STXT(A1;LIGNE($2:$255);1);MAJUSCULE(STXT(A1;LIGNE($2:$255);1)));0)+1;999)
valider avec maj+ctrl+entrée

Compter les AB + CD dans le champ A1:A5

AB|AB|CD|SD|AB
ZZ|AB|CD|SD|AB
CD|SD|ZZ

=SOMMEPROD((NBCAR(A1:A5)-NBCAR(SUBSTITUE(A1:A5;{"AB"."CD"};"")))/2)

Recherche du mot après la date

On veut extraire le mot après la date 040310 (Shell)

Paiement Carte 040310 Shell 2562 Montereau

MotAprèsDate

=GAUCHE(STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999);
CHERCHE(" ";STXT(A1;EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;LIGNE($1:$255);1)));0)+7;999))-1)
valider avec maj+ctrl+entrée