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

  • répondre 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

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

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

      • répondre 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

        Écrire un commentaire


        • *