Astuce Excel : Extraire des chaînes de texte

Publié le 11 mars 2013
par Sophie Marchand M.Sc.

Est-ce qu’il vous est déjà arrivé de travailler avec une liste de noms importés et de vouloir séparer le prénom, le nom du milieu et le nom de famille dans 3 colonnes distinctes? L’article suivant vous montre comment extraire des chaînes de texte dans Excel à l’aide des fonctions “Gauche” (“Left”), “Droite” (“Right”), “Trouve” (“Find”), “Substitue” (“Substitute”), “Supprespace” (“Trim”) et “Rept” (“Rept”).

 

Extraire les composantes d’un nom

Voici les formules à utiliser pour extraire les composantes d’un nom (qui comprend un “middle name”).

 

Extraire chaîne de texte

 

Voici plus d’information sur les fonctions utilisées:

 

Cliquez sur l’image pour l’agrandir

Extraire chaîne texte fonctions

 

Besoin de traduire des fonctions français-anglais?

Vous pouvez télécharger librement ce fichier de Traduction Fonctions (comprend toutes les fonctions d’Excel).

 

Vous aimeriez être un Ninja Excel et devenir la référence ultime de votre bureau, ou tout le moins ne plus avoir l’impression de stagner ? Développez des solutions innovatrices en explorant des fonctions et fonctionnalités souvent méconnues mais fort utiles avec nos formations pour devenir Ninja d’Excel.

 

Explications détaillées des formules pour extraire des chaînes de texte

AVERTISSEMENT: Des pépins techniques m’empêchent de représenter les guillemets correctement dans les explications ci-dessous. Svp vous référez à la première image pour bien comprendre de quelles expressions il s’agit.

 

Prénom

Décomposons la formule.

La partie =Find(” “, $B5) = 7. En effet, on demande à Excel de trouver l’espace (” “), dans ce cas-ci le premier qu’il croise, dans le nom Sophie M Marchand et de ramener sa position, qui est 7.

Ensuite, interprétons la formule dans son ensemble.

On demande à Excel d’aller dans la cellule comprenant l’expression “Sophie M Marchand” et de ne conserver que les 6 (7-1) caractères de gauche, i.e. S-O-P-H-I-E.

 

Milieu

Décomposons la formule.

La partie, Substitute($B5,$C5&” “,””) = M Marchand. En effet, on demande à Excel d’aller dans la cellule contenant l’expression “Sophie M Marchand”, de trouver l’expression “Sophie ” (remarquez que Sophie est suivi d’un espace), et de remplacer le tout par “” (i.e. rien du tout). En d’autres mots, “Sophie M Marchand”, devient alors “M Marchand”.

Ensuite, interprétons la formule dans son ensemble.

On demande à Excel d’aller dans l’expression “M Marchand”  et de remplacer ” Marchand” (remarquez l’espace devant “Marchand”) par “” (i.e., rien). On obtient alors que le “M”.

 

Nom

Décomposons la formule.

La partie =Substitute($B5,” “,rept(” “,100)), donne quelque chose comme ce qui suit, puisqu’on demande à Excel d’aller dans l’expression “Sophie M Marchand” et de remplacer les espaces vides par 100 ” ” (i.e., 100 espaces):

Substitute

La partie Right(Substitute($B5,” “,REPT(” “,100)),100), donne quelque chose comme ce qui suit, puisqu’on demande à Excel de ne retenir que les 100 caractères de droite de l’expression précédente (on obtient donc un certain nombre d’espaces, suivi du nom “Marchand”)

Rept

Ensuite, interprétons la formule dans son ensemble.

On demande ensuite à Excel de retirer tous les espaces de l’expression précédente, pour n’aboutir qu’avec “Marchand”.

 


Fichier d’accompagnement VIP à télécharger

Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.

 

Formation complémentaire

Développez vos compétences en analyse de données et exploitez les fonctions d’Excel qui permettent de manipuler, gérer et transformer des données qui proviennent de diverses sources et différents formats, sans faire intervenir Power Query ou le VBA, en suivant la formation Excel – Traitement, manipulation et analyse de données.

 

Voici quelques commentaires d’apprenants ayant suivi cette formation :

Le CFO masqué - Commentaires d'apprenants ayant suivi la formation : Excel - Traitement, manipulation et analyse de données

CFO-Masque_Formations-en-ligne_FBLa mission du CFO masqué est de développer les compétences techniques des analystes et des contrôleurs de gestion en informatique décisionnelle avec Excel et Power BI et favoriser l’atteinte de leur plein potentiel, en stimulant leur autonomie, leur curiosité, leur raisonnement logique, leur esprit critique et leur créativité.

9 réflexions sur “Astuce Excel : Extraire des chaînes de texte”

  1. Bonjour Sophie,
    une variante :
    Prénom =GAUCHE(B5;TROUVE(” “;B5)-1)
    Milieu =STXT(B5;TROUVE(” “;B5)+1;TROUVE(” “;B5;TROUVE(” “;B5)+1)-TROUVE(” “;B5))
    Nom =DROITE(B5;NBCAR(B5)-TROUVE(” “;B5;TROUVE(” “;B5)+1))

    et bien entendu aussi la possibilité d’utiliser données convertir sur un séparateur espace.

    Cordialement
    Michel PIRON

  2. Ping : ASTUCE EXCEL: EXTRAIRE DES CHAÎNES DE TEXTES | Bienvenue à BM Formation

  3. André Lafrenière

    Bonjour Sophie,

    Les formules Excel sont une bonne façon d’arriver à extraire des chaînes de caractères, mais parfois la création d’une fonction sur mesure est plus expéditive et peut être réutilisée partout dans le classeur !

    Voici une fonction «ExtraireNom()» sur mesure pour le cas :

    Public Function ExtraireNom(Texte As String) As String

    For i = 1 To Len(Trim(Texte))

    sT1 = Mid(Texte, i, 1)
    If i = 1 Then sT2 = sT1 Else sT2 = Mid(Texte, i – 1, 1)

    If sT1 ” ” Or sT2 ” ” Then
    ExtraireNom = ExtraireNom & sT1
    End If

    Next i

    End Function

    En passant, j’aime beaucoup vos chronique !!

    Cordialement,
    André Lafrenière

  4. Pouvez-vous me donner une formule qui s’applique aussi bien quand j’ai un prénom suivi de deux noms ou bien un prénom suivi d’un nom ? Merci d’avance. Il faut avouer que je ne suis pas bien fort dans ces exercices

  5. Bonjour,
    J’ai fait une extraction d’une base de données vers Excel.
    Dans une cellule, j’ai un texte qui apparait sur plusieurs ligne (alt+enter).
    Exemple: Numéro de pièce, Description, matériaux.
    Tas1
    Tasse pour cafe
    Porcelaine
    Lorsque que le tout est transféré en Excel, cela m’apparait de cette façon: Tas1Tasse pour cafePorcelaine
    Mais dans la cellule, j’ai toujours 3 lignes.
    Dans une autre cellule, je voudrais avoir une fonction qui va chercher seulement la première ligne du texte.
    Avez-vous une suggestion pour moi?

    Merci

    1. Bonjour Marie-Josée,

      Pour pouvoir vous aider, j’aurais besoin de l’extraction du fichier source, qui est probablement un fichier texte. Si ce n’est pas un document confidentiel ou si vous pouvez en reproduire un exemple, je vous invite à poser à nouveau votre question sur notre forum en y insérant votre fichier. Je pourrai alors vous répondre.

      Merci,

      Sophie

  6. Ping : Temp2 | Pearltrees

  7. Simplement, en français, j’aurais voulu extraire la même chose pour être certain de bien adapter la formule à ma version d’Excelb FR 2016
    Dans mon cas, si B17 contient le texte “E325 – Lactate de sodium”, j’écrivais
    = GAUCHE(B17; TROUVE(” – “,B17)-1))
    Et un message d’erreur apparait. “nous avons rencontré une erreur dans cette formule..”etc.
    Où est elle ?
    Merci de votre aide.

    1. Bonjour,

      D’abord, vous avez une parenthèse de trop et aussi, vous utilisez des points-virgules (;) et des virgules (,).

      Votre ordinateur est configuré pour utiliser l’un ou l’autre mais pas les deux.

      En français, de base, ça devrait être des points-virgules (;).

      Au plaisir,

      Sophie

Laisser un commentaire

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

Retour en haut