Contact: 514-605-7112 / info@lecfomasque.com

Astuce Excel: Extraire des chaînes de texte

Publié le : 11 mars 2013

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 faire 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).

.

Explications détaillées des formules

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 ».

.


NOTRE OFFRE DE FORMATIONS


Microsoft Most Valuable Professional

Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.

 

Consulter la liste des formations offertes

Pour info: 514-605-7112 ou info@lecfomasque.com

 

Sophie Marchand, M.Sc., CPA, CGA, MVP

Instigatrice du CFO masqué, Sophie Marchand est détentrice d’une M.Sc. en finance corporative, d’un titre comptable CPA, CGA et d’un titre MVP (Most valuable professional) Excel de Microsoft, et cumule plus de 14 années d’expérience dans le milieu des affaires. Elle se spécialise particulièrement en modélisation financière et en intelligence d’affaires. À ce titre, elle développe des modèles financiers rigoureux, des tableaux de bord sophistiqués et des outils de gestion performants. Elle offre ses services en tant que consultante, formatrice et conférencière.

  • reply PIRON ,

    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

    • reply ASTUCE EXCEL: EXTRAIRE DES CHAÎNES DE TEXTES | Bienvenue à BM Formation ,

      […] See on moncherwatson.wordpress.com […]

      • reply 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

        • reply Martin ,

          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

          • reply Marie-Josée Dufour ,

            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

            • reply Sophie Marchand, M.Sc., CPA, CGA, MVP ,

              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

            Leave a comment


            • *