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”).
.
.
Voici plus d’information sur les fonctions utilisées:
.
Cliquez sur l’image pour l’agrandir
.
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):
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”)
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”.
.
Le CFO masqué offre un vaste choix de formations en informatique décisionnelle avec Excel et Power BI, via un portail en ligne et à distance en temps réel, selon un calendrier. Si vous désirez organiser des formations privées, faites nous simplement parvenir un courriel à info@lecfomasque.com . Des certificats convenant aux normes de formation continue des divers ordres professionnels du Québec sont offerts pour l'ensemble des formations.
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
Ping : ASTUCE EXCEL: EXTRAIRE DES CHAÎNES DE TEXTES | Bienvenue à BM Formation
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
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
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
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
Ping : Temp2 | Pearltrees