Formule Excel pour extraire les derniers mots d'une cellule après un caractère spécial.

**jonathan_rio** · 14/04/2014, 18h05

Bonjour à tous,

Je suis nouveau sur ce forum.

J'ai cherché sans succès une formule qui me permette d'extraire les derniers mots des cellules de la colonne A à partir du dernier caractère spécial ":", puis les coller dans les cellules de B.
Chaque caractère ":" représente un niveau d'arborescence.

Exemple :

Colonne A	Colonne B
Direction:Espace temporaire:01- Transporter:Entreprises 1
Direction:Espace temporaire:01- Transporter:Entreprises 1:Fret National
Direction:Espace temporaire:01- Transporter:Entreprises 1:Fret National:CFL Cargo

Ma chaine de caractères varie tout le temps dans mon fichier Excel et la longueur de la chaine aussi.
Il faut que j’extraie les derniers mots de chaque ligne à partir du dernier caractère spécial ":" de A pour les mettre respectivement dans B.

Voilà ce que je dois obtenir :

Colonne A	Colonne B
Direction:Espace temporaire:01- Transporter	:Entreprises 1
Direction:Espace temporaire:01- Transporter:Entreprises 1	:Fret National
Direction:Espace temporaire:01- Transporter:Entreprises 1:Fret National	:CFL Cargo

Je vais ensuite avoir dans mon fichier Excel, d'autres lignes avec le même principe pour "Entreprise 2", "Entreprise 3", "Entreprise 4", etc... et avec une arborescence encore plus profonde. Dans l'exemple, j'ai mis 5 niveaux, mais je peux en avoir certains avec 10.
Mon but est de faire cela pour plus de 1000 lignes.

Ce serait super sympa si vous pouviez m'aider.

Merci par avance.

**Philippe Tulliez** · 14/04/2014, 18h31

Bonjour,
Dans les formules de la catégorie Texte, il y a trois formules permettant l'extraction d'une chaîne de caractères. GAUCHE, DROITE et STXT.
Ainsi si nous partons de cette chaîne Direction:Espace temporaire:01- Transporter:Entreprises 1 qui se trouverait en cellule A2, la formule ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DROITE(A2;13)

Permettrait d'extraire les 33 caractères de droite de la cellule A2 soit Entreprises 1.
Cependant comme le nombre de caractères à extraire est souvent une inconnue, il y a lieu de chercher le ou les caractères qui précèdent cette chaîne.
Deux formules permettent de chercher des caractères CHERCHE et TROUVE. CHERCHE n'est pas sensible à la casse, l'autre l'est.
La formule renvoie la position du premier caractère cherché

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=CHERCHE("transporter:";A2)

va donc renvoyer 33 et comme la chaîne transporter: à une longueur de 12 caractères
Il vaudrait mieux alors d'utiliser la formule STXT soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A2;CHERCHE("Transporter:";A2)+12;50)

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A2;CHERCHE("Transporter:";A2)+NBCAR("Transporter:");50)

La formule NBCAR permettant de renvoyer le nombre de caractères que contient une chaîne.
Pour l'explication complète des formules précitées je te renvoie vers l'aide d'excel accessible facilement par la boîte de dialogue Insertion de fonction.

**casefayere** · 14/04/2014, 20h13

Bonosoir le forum, Philippe, jonathan_rio

Peut-être t'inspirer de cette discussionhttp://www.developpez.net/forums/d13...ce-d-caratere/

**jonathan_rio** · 15/04/2014, 09h37

Envoyé par Philippe Tulliez

Bonjour,
Dans les formules de la catégorie Texte, il y a trois formules permettant l'extraction d'une chaîne de caractères. GAUCHE, DROITE et STXT.
Ainsi si nous partons de cette chaîne Direction:Espace temporaire:01- Transporter:Entreprises 1 qui se trouverait en cellule A2, la formule ci-dessous

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DROITE(A2;13)

Permettrait d'extraire les 33 caractères de droite de la cellule A2 soit Entreprises 1.
Cependant comme le nombre de caractères à extraire est souvent une inconnue, il y a lieu de chercher le ou les caractères qui précèdent cette chaîne.
Deux formules permettent de chercher des caractères CHERCHE et TROUVE. CHERCHE n'est pas sensible à la casse, l'autre l'est.
La formule renvoie la position du premier caractère cherché

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=CHERCHE("transporter:";A2)

va donc renvoyer 33 et comme la chaîne transporter: à une longueur de 12 caractères
Il vaudrait mieux alors d'utiliser la formule STXT soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A2;CHERCHE("Transporter:";A2)+12;50)

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A2;CHERCHE("Transporter:";A2)+NBCAR("Transporter:");50)

La formule NBCAR permettant de renvoyer le nombre de caractères que contient une chaîne.
Pour l'explication complète des formules précitées je te renvoie vers l'aide d'excel accessible facilement par la boîte de dialogue Insertion de fonction.

Bonjour Philippe,

Je te remercie pour ta réponse.
Ta solution fonctionne bien si la chaîne de caractères que je dois extraire est toujours la même, mais toutes mes lignes possèdent une chaîne et une longueur différente. (et c'est vrai que j'ai manqué de précision à ce niveau là, désolé).

Exemple :

Colonne A	Colonne B
Direction:Espace temporaire:01- Transporter:Entreprises 1
Direction:Espace temporaire:04- Transporter:Entreprises 1:Fret National
Direction:Espace temporaire:04- Transporter:Entreprises 1:Fret National:CFL Cargo

Ma chaine de caractères varient presque tout le temps dans mon fichier Excel et la longueur de la chaine aussi.
Il faut que j'obtienne ça :

Colonne A	Colonne B
Direction:Espace temporaire:01- Transporter	:Entreprises 1
Direction:Espace temporaire:04- Transporter:Entreprises 1	:Fret National
Direction:Espace temporaire:04- Transporter:Entreprises 1:Fret National	:CFL Cargo

Dans ce là, connais-tu une solution qui me permettrait d'extraire les chaînes de A respectivement dans B ?

Désolé de ne pas avoir été plus précis dès le départ.
Je vais corriger l'intitulé du message.

Merci.

**Philippe Tulliez** · 15/04/2014, 10h04

Bonjour,

Ta solution fonctionne bien si la chaîne de caractères que je dois extraire est toujours la même, mais toutes mes lignes possèdent une chaîne et une longueur différente

Je pense pourtant l'avoir expliqué

Cependant comme le nombre de caractères à extraire est souvent une inconnue, il y a lieu de chercher le ou les caractères qui précèdent cette chaîne.
Deux formules permettent de chercher des caractères CHERCHE et TROUVE. CHERCHE n'est pas sensible à la casse, l'autre l'est.
La formule renvoie la position du premier caractère cherché

mais il faut bien entendu qu'un ou plusieurs caractères à chercher soient communs sur toutes les lignes de la colonne A pour que cela fonctionne.

J'avoue ne pas comprendre le deuxième tableau.
Par exemple dans la première ligne de ce tableau, comment peux-tu extraire en colonne B la chaîne :Entreprises 1 alors que tu as ce texte Direction:Espace temporaire:01- Transporter en colonne A

[EDIT]
La question principale, si on se réfère au titre de la discussion

Formule Excel pour extraire les derniers mots d'une cellule après un caractère spécial.

Quel est ce caractère spécial ?

**papouclo** · 15/04/2014, 10h55

Bonjour,
La chaine initiale étant en A1, écrire la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";""))));100)

à recopier pour les autres cellules contenant des chaines d'où l'on doit extraire la fin à partir du dernier double point.
Cordialement
Claude

**jonathan_rio** · 15/04/2014, 11h05

Re-bonjour Philippe,

Pour répondre à ta question :

Envoyé par Philippe Tulliez

J'avoue ne pas comprendre le deuxième tableau.
Par exemple dans la première ligne de ce tableau, comment peux-tu extraire en colonne B la chaîne :Entreprises 1 alors que tu as ce texte Direction:Espace temporaire:01- Transporter en colonne A

On peut extraire dans la colonne B ":Entreprises 1" car dans la A on avait avant la formule "Direction:Espace temporaire:01- Transporter:Entreprises 1"
J'avais en tête de couper ":Entreprises 1" de la colonne A pour mettre cette valeur dans la colonne B. Tu crois que c'est possible de faire comme ça ?

Le caractère spécial est ":".

Sinon, j'ai trouvé cette formule :
"=SI(NBCAR(A2)-NBCAR(SUBSTITUE(A2;":";""))>2;DROITE(A2;NBCAR(A2)-CHERCHE("|";SUBSTITUE(A2;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";""))-0)));A2)"
(merci à Dominique pour cette formule).

Elle fonctionne chez moi, ça me met dans la colonne B les derniers mots après le dernier caractère ":" (ex. "Entreprises 1" en B)

Par contre, ça n'enlève pas "Entreprises 1" de la colonne A; en effet j'ai encore -> "Direction:Espace temporaire:01- Transporter:Entreprises 1".
Il me faudrait la formule qui enlève "Entreprises 1" de la colonne A, pour avoir -> ""Direction:Espace temporaire:01- Transporter"

Cordialement,

**Philippe Tulliez** · 15/04/2014, 11h09

Bonjour Claude,

J'ai cherché sans succès une formule qui me permette d'extraire les derniers mots des cellules de la colonne A à partir du dernier caractère spécial ":", puis les coller dans les cellules de B.
Chaque caractère ":" représente un niveau d'arborescence.

Je ne vois nulle part dans la question le caractère "|", chez moi cela se traduit par ":". Je dois avoir une autre codification de mes caractères (je suis sous Linux)

**casefayere** · 15/04/2014, 11h15

Bonjour ou Rebonjour à tou(te)s,

A moins que je ne comprenne pas mais il me semble que la proposition du lien que j'ai faite sur mon Post "#3" peut être adaptée.

**papouclo** · 15/04/2014, 12h46

Bonjour Philippe,Dominique
Ce genre de problème avait été traité il y a peu de temps (c'est certainement la référence donnée par Dominique mais je n'ai pas vérifié ayant encore en mémoire le sujet).
La démarche consiste à remplacer le caractère spécial mais présentant plusieurs occurrences dans la chaine, ici le double point ":", par un autre caractère unique pour la position requise,
puis de traiter la chaine à partir de ce caratère spécial unique.
Il faut donc compter le nombre d'occurrences du double point dans la chaine, ce qui est donné par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";"")

ensuite remplacer le dernier double point par exemple par le caractère "|" , ce qui est obtenu par :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";""))

rechercher la position de ce "|" dans la chaine :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CHERCHE("|";SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";"")))

enfin récupérer la fin de la chaine au moyen de la fonction STXT pour ne pas avoir à calculer le nombre de caractères comme avec DROITE

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";"")));100)

Ce qui me permet de m'apercevoir que j'avais mis une parenthèse fermante en trop dans ma réponse. Toutes mes excuses !
Cordialement
Claude

**Philippe Tulliez** · 15/04/2014, 13h01

Bonjour Jonathan,

On peut extraire dans la colonne B ":Entreprises 1" car dans la A on avait avant la formule "Direction:Espace temporaire:01- Transporter:Entreprises 1"
J'avais en tête de couper ":Entreprises 1" de la colonne A pour mettre cette valeur dans la colonne B.

J'avais bien compris ce qu'il fallait faire mais ce n'est pas ce que tu illustres dans le Tableau 2 de ton intervention de 9h37 numérotée #4

**jonathan_rio** · 15/04/2014, 14h19

Envoyé par Philippe Tulliez

J'avais bien compris ce qu'il fallait faire mais ce n'est pas ce que tu illustres dans le Tableau 2 de ton intervention de 9h37 numérotée #4

C'est en effet pas toujours évident de bien expliquer.
A partir du 1er tableau, comment obtenir le 2ème tableau ?

Avec la formule que j'ai trouvé et que j'ai collé plus haut, j'ai en parti résolu mon problème.

Il me reste plus qu'à trouver la formule permettant de supprimer dans la colonne A les derniers mots de la chaine après le dernier caractère ":"
(cf. le 1er tableau : je veux supprimer pour la 1ère ligne "Entreprises 1", pour la 2ème "Fret National", pour la 3ème "CFL Cargo", etc...)

**jonathan_rio** · 15/04/2014, 14h33

Envoyé par papouclo

La chaine initiale étant en A1, écrire la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";""))));100)

à recopier pour les autres cellules contenant des chaines d'où l'on doit extraire la fin à partir du dernier double point.
Cordialement
Claude

Bonjour Claude,
Ta formule fonctionne très bien, je t'en remercie !
J'ai donc ce que je souhaite dans la colonne B.

Maintenant comment adapter cette formule pour extraire la chaine jusqu'au dernier double point ?

Je m'explique, par exemple avant ta formule j'avais dans ma colonne A "Direction:Espace temporaire:01- Transporter:Entreprises 1" et ma colonne B était vide.
Avec ta formule, j'ai dans ma colonne B ":Entreprises 1" et c'est ce que je voulais !

Il me reste maintenant à enlever de ma colonne A ce que j'ai mis dans ma colonne B, c'est-à-dire que je dois avoir en A seulement "Direction:Espace temporaire:01- Transporter".

Tu comprends ce que je veux dire ?

**papouclo** · 15/04/2014, 18h51

Re,
La chaine initiale étant en A1, écrire en C1 la formule donnée précédemment (la correcte, c'est à dire finalement la première formule donnée)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A1;CHERCHE("|";SUBSTITUE(A1;":";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;":";""))));100)

En B1, écrire alors la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=GAUCHE(A1;CHERCHE(C1;A1)-1)

Recopier les formules vers le bas.
Il suffit ensuite de masquer la colonne A pour avoir le deuxième tableau souhaité (mais les deux colonnes du tableau obtenu seront B et C).
Il vaut mieux ne pas tenter d'obtenir le tableau final sur les colonnes A et B (et les mêmes lignes) comme tu le demandes au moyen de formules à moins d'aimer les
avertissements de référence circulaire.
Si ma proposition ne te convient pas, il faudra réfléchir à du code VBA.
Cordialement
Claude

**jonathan_rio** · 16/04/2014, 10h29

Bonjour à tous,

J'ai réussi avec votre aide à résoudre mon problème.

Pour avoir la dernière partie de chaque ligne après la dernière occurence ":", j'ai appliqué dans la colonne C cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SI(NBCAR(A2)-NBCAR(SUBSTITUE(A2;":";""))>2;DROITE(A2;NBCAR(A2)-CHERCHE("|";SUBSTITUE(A2;":";"|";NBCAR(A2)-NBCAR(SUBSTITUE(A2;":";""))-0)));A2)

Après, il fallait que j'enlève de A la partie que j'avais mis dans la colonne C, j'ai donc appliqué dans la colonne D cette formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A2;1;CHERCHE("|";SUBSTITUE(A2;":";"|";NBCAR(A2)-NBCAR(SUBSTITUE(A2;":";""))))-1)

Ainsi, j'avais dans C et dans D les valeurs attendues.

Ensuite, je voulais avoir les valeurs de C dans la colonne A et les valeurs de D dans la colonne B.
J'ai donc créé un 2ème document Excel où j'ai tout simplement copié les valeurs de C et de D de mon 1er doc et j'ai fais un collage spécial (par valeur) dans mon 2ème doc.

Je vous remercie tous pour votre aide précieuse.

**Hitman_11** · 23/04/2016, 17h34

Bonjour je reprends le fil de cette discussion, pour solliciter votre aide.
Je ne parviens pas à comprendre la subtilité des formules d'extraction proposée.
Dans mon cas, je souhaiterais extraire, dans une cellule qui contient, le nom et les prénoms.
Chaque mot est séparé par un espace.
Je souhaiterais extraire, dans une cellule, le nom et le premier prénom, dans une autre cellule le nom et le deuxième prénom.

Pouvez vous me proposer une solution? Avec une explication que je puisse comprendre le fonctionnement.

Je vous remercie pour votre aide.

**casefayere** · 23/04/2016, 18h52

Bonsoir,

tu aurais du faire un nouveau sujet mais en attendant va sur ce lien https://support.office.com/fr-fr/art...c-1bf66e219ea8 qui devrait t'aider, reviens ensuite pour des précisions, attention ! tu peux avoir des noms composés (les "de machin, etc)

**Hitman_11** · 24/04/2016, 23h20

Envoyé par casefayere

Bonsoir,

tu aurais du faire un nouveau sujet mais en attendant va sur ce lien https://support.office.com/fr-fr/art...c-1bf66e219ea8 qui devrait t'aider, reviens ensuite pour des précisions, attention ! tu peux avoir des noms composés (les "de machin, etc)

Bonsoir, je te remercie pour cette page, mais je l'ai lu et relu sans rien comprendre à ces fonctions.

Formule Excel pour extraire les derniers mots d'une cellule après un caractère spécial. [XL-2007]

Excel

Discussions similaires

Partager

Partager