Exercices Corrigés
Fichier d'exercices lié à l'article traitant cette fonction, disponible su
Rappel:
Extraction du matricule au sein d'une chaîne de texte
Nous disposons ci-dessous d'une liste de salariés avec leur nom, matricule, service. Ces informati
suivi d'un tiret et d'un espace, le matricule est toujours de 5 caractères.
Indiquer en colonne D le matricule:
Salarié Matricule
D. Duval - XL334 - Comptabilité
P. Gilot - XI552 - Commercial
A. Maréchal - ZX832 - Entretien
Extraction prénom / nom
A partir de la liste ci-dessous, extraire les prénoms en colonne D et les noms en colonne E
Vous pouvez-vous aider d'autres fonctions comme NBCAR() TROUVE() SUBSTITUE() SUPPRES
Salarié Prénom Nom
PORTAL Marie-Pierre
DE SAINT ALBIN Vianey
DUCHEMIN Henri
Reconstitution de dates
Le tableau ci-dessous indique des dates, en format texte, année mois jour.
En colonne D reconstituer les dates au format date - aidez-vous des fonctions DROITE() GAUCHE
Date
20090503
20091204
20090815
Extraction d'un numéro client
Le tableau ci-dessous présente une liste de codifications clients, nous voulons en extraire le numé
Les codifications sont toujours de même longeur: 1 lettre suivie d'un tiret, 4 chiffres, et enfin un esp
Inscrire en colonne C le numéro client sur 4 caractères.
Code N° Client
A-6573 ARG
A-7650 TZN
F-0879 FRA
U-4351 CAN
Bon
Faux
facile
moyen
aitant cette fonction, disponible sur ce lien: difficile
atricule, service. Ces informations sont séparées par un espace
les noms en colonne E
VE() SUBSTITUE() SUPPRESPACE() etc …
fonctions DROITE() GAUCHE() et DATE() -
us voulons en extraire le numéro.
tiret, 4 chiffres, et enfin un espace suivi du code pays.
Exercices Corrigés
Extraction du matricule au sein d'une chaîne de texte
Nous disposons ci-dessous d'une liste de salariés avec leur nom, matricule, service. Ces informations
suivi d'un tiret et d'un espace, le matricule est toujours de 5 caractères.
Indiquer en colonne D le matricule:
Salarié Matricule
D. Duval - XL334 - Comptabilité XL334
P. Gilot - XI552 - Commercial XI552
A. Maréchal - ZX832 - Entretien ZX832
Formule En D25 : =STXT(B25;CHERCHE(" - ";B25)+3;5)
s
La longueur du texte à extraire est de 5 carac
Le début de la chaîne à extraire se situe exactement après
La fonction CHERCHE() permet d'indiquer la position de ce
A ce rérultat nous ajoutons 3, pour démarrer après la chaîn
La chaîne à extraire se trouve en cellule B25
Cette formule permet donc d'extraire 5 caractères de la cellule B25, à partir de la 12ème position
Extraction prénom / nom
A partir de la liste ci-dessous, extraire les prénoms en colonne D et les noms en colonne E
Vous pouvez-vous aider d'autres fonctions comme NBCAR() TROUVE() SUBSTITUE() SUPPRESPAC
Salarié Prénom Nom
PORTAL Marie-Pierre Marie-Pierre PORTAL
DE SAINT ALBIN Vianey Vianey DE SAINT ALBIN
DUCHEMIN Henri Henri DUCHEMIN
Formule En D52 : =STXT(B52;TROUVE("zzz";SUBSTITUE(B52;" ";"zzz";NBCAR(B52)-NBCAR(SUBSTIT
s
Commentaires:
NBCAR(B52) indique le nombre de carctères contenus en B52: 21
SUBSTITUE(B52;" ";"") remplace dans la cellule B52 les espaces par du vide
DESAINTALBINVianey
Cette fonction est encadrée par NBCAR() afin de compter le nombre caractères sans les espaces
18
La différence entre les deux, soit NBCAR(B52)-NBCAR(SUBSTITUE(B52;" ";"")), indique donc le nom
présents dans la cellule B52 : 3 Formule : =NBCAR(B52)-NBCAR(SUBSTIT
Ce résultat est intégré dans une fontion SUBTITUE(), afin de nous donner la dernière occurence du ca
SUBSTITUE(B52;" ";"zzz";NBCAR(B52)-NBCAR(SUBSTITUE(B52;" ";""))) revient donc à écrire SUBS
ce qui signifie que l'on remplace en cellule B53, le 3ème espace (donc le dernier) par zzz :
DE SAINT ALBINzzzVianey Formule : =SUBSTITUE(
Pourquoi zzz ? Nous pouvons mettre autre chose, du moment que cette chaîne de caractère n'existe p
Nous aurions pu utiliser þ ou ‰ à la place de zzz, le but étant de mettre quelque chose qui a peu de c
Une fois notre dernier espace remplacé par zzz, nous utilisons la fonction TROUVE() pour connaître la
TROUVE("zzz";SUBSTITUE(B52;" ";"zzz";NBCAR(B52)-NBCAR(SUBSTITUE(B52;" ";""))))
la chaîne zzz débute en 15ème position: 15 Formule : =TROUVE("zzz";SUBSTITUE(B52
Tout ceci revient à dire que le dernier espace se trouve en position 15 dans la cellule B52, donc que le
(d'où le +1 dans la formule)
La fonction STXT va donc extraire de la cellule B52, la chaîne de caractère commençant en position 1
Nous aurions pu calculer la longueur exacte à extaire, mais pour simplifier nous considérons que la lon
de la cellule, nous calculons donc STXT(B52;16;21) : Vianey Formule : =STXT(B52;16
Ces formules paraissent plus simples si on les décompose sur plusieurs colonnes :
Nbre Préparation pour fonction
Salarié
d'espaces TROUVE()
PORTAL Marie-Pierre 1 PORTALzzzMarie-Pierre
DE SAINT ALBIN Vianey 3 DE SAINT ALBINzzzVianey
DUCHEMIN Henri 1 DUCHEMINzzzHenri
=SUBSTITUE(B99;" ";"zzz";D99)
=TROUVE("zzz
Reste le nom en E52 :
Formule En E52 =SUPPRESPACE(SUBSTITUE(B52;D52;""))
s
Formule
s
Décomposons:
SUBSTITUE(B52;D52;"") remplace en cellule B52, le contenu de la cellule D52
Afin de retirer l'espace inutile à la fin du nom, la formule est encadrée par SUP
Reconstitution de dates
Le tableau ci-dessous indique des dates, en format texte, année mois jour.
En colonne D reconstituer les dates au format date - aidez-vous des fonctions DROITE() GAUCHE() e
Date
20090503 5/3/2009
20091204 12/4/2009
20090815 8/15/2009
Formule En C128 : =DATE(GAUCHE(B128;4);STXT(B128;5;2);DROITE(B128;2))
s Décomposons :
DROITE(B128;2) renvoie les 2 derniers caractères à droite de la cellule B128 s
GAUCHE(B128;4) renvoie les 4 premiers caractères à gauche de la cellule B12
STXT(B128;5;2) extrait 2 caractères à partir de la position 5
La fonction DATE() permet de transformer le tout en une vraie date
Synthaxe de la fonction : DATE(année;mois;jour)
Extraction d'un numéro client
Le tableau ci-dessous présente une liste de codifications clients, nous voulons en extraire le numéro.
Les codifications sont toujours de même longeur: 1 lettre suivie d'un tiret, 4 chiffres, et enfin un espace
Inscrire en colonne C le numéro client sur 4 caractères.
Code N° Client
A-6573 ARG 6573
A-7650 TZN 7650
F-0879 FRA 0879
U-4351 CAN 4351
Formule En C158 : =STXT(B158;3;4)
s
La chaîne de caractères à extraire est en B158, l'extraction commence à la pos
La fonction STXT() renvoie les données sous forme de texte, même s'il s'agit d
En l'occurrence ceci permet de conserver le zéro en première position sur la lig
vice. Ces informations sont séparées par un espace
extraire est de 5 caractères.
itue exactement après la suite de 3 caractères: " - "
diquer la position de cette chaîne :
9 Formule : =CHERCHE(" - ";B25)
émarrer après la chaîne " - " qui mesure 3 caractères.
de la 12ème position
n colonne E
TITUE() SUPPRESPACE() etc …
52)-NBCAR(SUBSTITUE(B52;" ";""))))+1;NBCAR(B52))
Formule : =NBCAR(B52)
SAINTALBINVianey Formule : =SUBSTITUE(B52;" ";"")
sans les espaces
Formule : =NBCAR(SUBSTITUE(B52;" ";""))
), indique donc le nombre d'espaces
52)-NBCAR(SUBSTITUE(B52;" ";""))
rnière occurence du caractère espace contenu dans la cellule B52
nt donc à écrire SUBSTITUE(B52;" ";"zzz";3)
r) par zzz :
rmule : =SUBSTITUE(B52;" ";"zzz";NBCAR(B52)-NBCAR(SUBSTITUE(B52;" ";"")))
de caractère n'existe pas déjà dans le nom.
e chose qui a peu de chance de se trouver dans un prénom
UVE() pour connaître la position de cette chaîne
B52;" ";"")))) revient donc à écrire TROUVE("zzz";"DE SAINT ALBINzzzVianey")
"zzz";SUBSTITUE(B52;" ";"zzz";NBCAR(B52)-NBCAR(SUBSTITUE(B52;" ";""))))
ellule B52, donc que le prénom commence en position 16
mmençant en position 16, jusqu'à la fin.
considérons que la longeur ne peut pas excéder le nombre de caractère total
rmule : =STXT(B52;16;21)
extraction 1
Position du
caractère
zzz
plus loin
7 Marie-Pierre
15 Vianey
9 Henri
9;" ";"zzz";D99) =STXT(B99;G99+1;NBCAR(B99))
=TROUVE("zzz";E99)
ntenu de la cellule D52 par vide, il ne reste ainsi que le nom:
DE SAINT ALBIN Formule : =SUBSTITUE(B52;D52;"")
est encadrée par SUPPRESPACE() :
DE SAINT ALBIN Formule : =SUPPRESPACE(G113)
DROITE() GAUCHE() et DATE() -
ite de la cellule B128 soit le jour
03 Formule : =DROITE(B128;2)
auche de la cellule B128 soit l'année
2009 Formule : =GAUCHE(B128;4)
05 Formule : =STXT(B128;5;2)
e vraie date
5/3/2009 Formule : =DATE(G140;G143;G136)
en extraire le numéro.
res, et enfin un espace suivi du code pays.
ion commence à la position 3, et est d'une longueur de 4 caractères
exte, même s'il s'agit d'une suite de chiffres.
mière position sur la ligne 3
