formule separer nom prenom

C@thy · 29 Novembre 2011

Bonjour le forum,

je suis en train d'essayer de trier par nom une liste complètement mal foutue.

Le but du jeu est donc : obtenir une colonne nom et une colonne prénom.

J'ai commencé mais je bloque un peu...😕

j'essaie par formules, mais j'imagine qu'on doit aussi pouvoir le faire par macro vu que le nom est totalement en majuscules...

Si vous pouviez m'apporter un début de solution, je vous en remercierais grandement.
je cherche de mon côté mais je ne vais pas très vite (suis dans les matricielles...)

je joins un exemple avec des noms bidon...

Merci à vous😉

Bises

C@thy

Guest · 29 Novembre 2011

Re : formule separer nom prenom

Bonjour C@thy🙂,

Si tous les noms sont en majuscules, appliquée à ton classeur, cette macro séparera les noms et prénoms en colonne F:G

Code:

Sub NomsPrenoms()
With ActiveSheet
    Dim T: T = .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
    Dim i As Integer, nb As Integer, j As Integer
    Dim un As Variant, res As Variant
    nb = UBound(T)
    ReDim res(1 To nb, 1 To 2)
    For i = 1 To nb
     un = Split(Trim(Replace(Replace(T(i, 1), "M.", ""), "Mme", "")), " ")
     For j = 0 To UBound(un)
        If UCase(un(j)) = un(j) Then
            res(i, 2) = res(i, 2) & " " & un(j)
        Else
            res(i, 1) = res(i, 1) & " " & un(j)
        End If
     Next
      res(i, 1) = Trim(res(i, 1))
      res(i, 2) = Trim(res(i, 2))
      
    Next
    .Range("F2:G2").Resize(nb).Value = res
    End With
End Sub

A+

C@thy · 29 Novembre 2011

Re : formule separer nom prenom

Merci Hasco,

je m'en doutais, une solution macro paraissait plus simple.

Je teste et je te dis.

OK, j'ai testé, c'est super,
peux-tu me dire comment on modifie
un = Split(Trim(Replace(Replace(T(i, 1), "M.", ""), "Mme", "")), " ") pour rajouter Mle (on ne sait jamais...)
j'ai essayé
un = Split(Trim(Replace(Replace(T(i, 1), "M.", ""), "Mme", ""), "Mlle", "")), " ")

mais il en veut pas...

Edit : OK, j'ai trouvé, c'est
un = Split(Trim(Replace(Replace(Replace(T(i, 1), "M.", ""), "Mme", ""), "Mlle", "")), " ")

En te remerciant,

Bises

C@thy

Guest · 29 Novembre 2011

Re : formule separer nom prenom

Re, Cathy,

Peut-être ce serait plus efficace de faire les remplacements directement dans la feuille avant de lancer la macro. Car s'il y a des M (sans point) ou Mme. (avec point), il ne seront pas remplacer. Les utilisateurs qui rentrent les adresses sont parfois farfelus(comme moi😎)

Fait toi à l'aide de l'enregistreur de macro, une macro de remplacements que tu pourras adapter suivant tes besoins.

Personnellement je scindrais en deux les actions. 1 - nettoyage 2 - extraction noms et prénoms.

A+

BOISGONTIER · 29 Novembre 2011

Re : formule separer nom prenom

Bonjour,

Par formule

Prénom:
=GAUCHE(SUBSTITUE(A2;"M. ";"");EQUIV(VRAI;EXACT(STXT(SUBSTITUE(A2;"M. ";"");LIGNE($1:$255);3);MAJUSCULE(STXT(SUBSTITUE(A2;"M. ";"");LIGNE($1:$255);3)));0))
Valider avec Maj+ctrl+entrée

Nom:
=STXT(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");EQUIV(VRAI;EXACT(STXT(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");LIGNE($1:$255);3);MAJUSCULE(STXT(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");LIGNE($1:$255);3)));0)+1;999)

JB

C@thy · 29 Novembre 2011

Re : formule separer nom prenom

Hasco à dit:
Les utilisateurs qui rentrent les adresses sont parfois farfelus

alors là, je ne suis pas du tout d'accord avec toi... sur le parfois!!!

Tu as raison il faut d'abord nettoyer,
perso je ferais une formule :
=STXT(A2;CHERCHE(" ";A2)+1;100)...

Waaaaaaaaaaooooooooooooooo!!!
JB, encore une fois je suis bluffée, une seule colonne alors que j'en ai fait 5...😱

juste un truc, ça fonctionne super bien avec M. mais pas Mme ou Mlle...😱

Mille mercis, c'est impressionnant!!!

Bises

C@thy

C@thy · 29 Novembre 2011

Re : formule separer nom prenom

P.S.

JB, c'est bizarre, la formule du nom marche parfaitement même si j'ai Mlle devant le prénom,
par contre le formule du prénom me conserve Mme ou Mlle si ils se trouvent avant le prénom.

Bises

C@thy

BOISGONTIER · 29 Novembre 2011

Re : formule separer nom prenom

Voir pj

Code:

=GAUCHE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");EQUIV(VRAI;EXACT(STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");LIGNE($1:$255);3);MAJUSCULE(STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");LIGNE($1:$255);3)));0))

=STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");EQUIV(VRAI;EXACT(STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");LIGNE($1:$255);3);MAJUSCULE(STXT(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"M. ";"");"Mme ";"");"Mle ";"");LIGNE($1:$255);3)));0)+1;999)

JB

C@thy · 29 Novembre 2011

Re : formule separer nom prenom

OK, je te remercie grandement,JB,
belles formules et ça fonctionne nickel!!!

Bises

C@thy

JNP · 29 Novembre 2011

Re : formule separer nom prenom

Bonjour le fil 🙂,
Sachant que Cathy connait les RegExp et ayant vu que JB commençait à s'y interresser, une solution RegExp assez complète (seule obligation, les prénoms en minuscules avec la première lettre de chaque prénom en majuscule, les noms tout en majuscule) 😛...

Code:

Function Prénom(ATrouver As String) As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(?!M\.)(?!Mme)(?!Mlle)([A-Z]['a-zàâéèëêïîöôüûy]+-* *)+"
        If .test(ATrouver) Then
    Prénom = .Execute(ATrouver)(0)
    End If
End With
End Function

Code:

Function Nom(ATrouver As String) As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "(?!M\.)(?!Mme)(?!Mlle)(['A-Z]{2,}-* *)+"
        If .test(ATrouver) Then
    Nom = .Execute(ATrouver)(0)
    End If
End With
End Function

Bises Cathy et bonne suite 😎

C@thy · 7 Décembre 2011

Re : formule separer nom prenom

J'aime bien ta soluce, JNP car elle est originale...

alors voilà, dans le genre tordu... (comme d'hab, dira JNP),

j'aimerais combiner le "séparer nom prénom" et le "retirer les accents du prénom", dont voici la fonction :

Code:

Public Function sansAccents(txt As String) As String
Application.Volatile
For i% = 1 To Len(txt)
    Select Case Mid(txt, i, 1)
        Case Chr(192) To Chr(197), Chr(224) To Chr(229)
            char = "A"
        Case Chr(232) To Chr(235), Chr(200) To Chr(203)
            char = "E"
        Case "í", "ì", "î", "ï", "Í", "Ì", "Î", "Ï"
            char = "I"
        Case "ó", "ò", "ô", "ö", "õ", "Ó", "Ò", "Ô", "Ö", "Õ", Chr(248)
            char = "O"
        Case "ú", "ù", "û", "ü", "Ú", "Ù", "Û", "Ü"
            char = "U"
        Case "ÿ", "ý", Chr(221), Chr(159)
            char = "Y"
        Case "ñ", "Ñ"
            char = "N"
        Case "ç", Chr(199)
            char = "C"
        Case Chr(230), Chr(198)
            char = "AE"
        Case Chr(138)
            char = "S"
        Case Chr(140), Chr(156)
            char = "OE"
    Case Else
        char = UCase(Mid(txt, i, 1))
    End Select
    laChaine = laChaine & char
Next i
sansAccents = Trim(laChaine)
End Function

Arf et Niark!... Et en plus, je suis sûre que c'est possible...
mais je commande une caisse d'aspirine pour qui voudrait chercher sur le sujet...

Bises

C@thy

C@thy · 7 Décembre 2011

Re : formule separer nom prenom

Oups! je viens de tester la superfonction de JB,
mais cette fois-ci j'ai juste les noms et prénoms à la suite, sans M. Mme ou Mlle
(ils font ce qu'ils veulent les utilisateurs, un coup la civilité, un coup pas...)
donc le but c'est de séparer le nom (tout en majuscules) du prénom (1ère lettre en majuscules, ou 2 majuscules comme dans Marie-Noëlle),
tout en retirant les accents du prénom,

cela me permettra de comparer 2 fichiers et de restituer les informations manquantes lorsque l'égalité nom prénom sans accent est trouvée...

Voici un mini exemple, je récupère dans la feuille2 les infos qui sont en jaune dans la feuille1

il faudra rajouter les colonnes intermédiaires prénom et nom avec la macro-fonction qui va bien,
c'est à dire qui sépare le nom (tout en majuscules) du prénom sans accent ni téma ni cédille...

Si vous avez une idée, je cherche aussi de mon côté et vous tiendrai au courant de mes trouvailles

Encore merci à vous

Bises

C@thy

jp14 · 7 Décembre 2011

Re : formule separer nom prenom

Bonjour le fil

J'arrive en retard

Ci dessous une macro qui tient compte des prénoms avec un espace comme El Hassan.
La recherche se fait en utilisant le code ascii.

Code:

Sub travdem()
Dim Cellule As Range
Dim Nomfeuille1 As String
Dim Col As String
Dim data1 As String
Dim pos As Integer
Dim Civilite As String
Dim prenom As String
Dim nom As String
Dim pre As Boolean
Dim i As Long
'parametre
' pour boucler sur la colonne 1
Nomfeuille1 = "Nom Prénom"
Col = "A"
With Sheets(Nomfeuille1)
 For Each Cellule In .Range(Col & "2:" & Col & .Range(Col & .Rows.Count).End(xlUp).Row)
 pos = InStr(2, Cellule, " ")
   Civilite = Trim(Mid(Cellule, 1, pos))
data1 = Trim(Replace(Cellule, Civilite, ""))
 
 pos = 0
 Do
    pre = False
     pos = InStr(pos + 1, data1, " ")
     For i = pos To Len(data1)
        If Asc(Mid(data1, i, 1)) > Asc("a") - 1 Then
            pre = True
            Exit For
        End If
    Next i
' si pre = false alors l'espace est le séparateur entre nom et prenom
    If pre = False Then Exit Do
    
Loop
nom = Trim(Mid(data1, pos, 1000))
prenom = Trim(Replace(data1, nom, ""))
.Range("b" & Cellule.Row) = Civilite
.Range("c" & Cellule.Row) = prenom
.Range("d" & Cellule.Row) = nom


Next Cellule

End With
End Sub

A tester

JP

C@thy · 7 Décembre 2011

Re : formule separer nom prenom

OK, je teste ça tout de suite,

de mon côté, j'ai récupéré ceci :

Code:

Public Function Prenom(zone As Range)
Application.Volatile
Dim tmpStr() As String, i As Long
    Prenom = ""
    tmpStr = Split(zone(1, 1).Text, " ")
    For i = LBound(tmpStr) To UBound(tmpStr)
        If WorksheetFunction.Proper(tmpStr(i)) = tmpStr(i) Then Prenom = Prenom & tmpStr(i) & " "
    Next i
    If Len(Prenom) <> 0 Then Prenom = Left(Prenom, Len(Prenom) - 1)
End Function

Code:

Public Function Nom(zone As Range)
Application.Volatile
Dim tmpStr() As String, i As Long
    Nom = ""
    tmpStr = Split(zone(1, 1).Text, " ")
    For i = LBound(tmpStr) To UBound(tmpStr)
        If UCase(tmpStr(i)) = tmpStr(i) Then Nom = Nom & tmpStr(i) & " "
    Next i
    If Len(Nom) <> 0 Then Nom = Left(Nom, Len(Nom) - 1)
End Function

Merci à toi jp14 😉

C@thy

C@thy · 7 Décembre 2011

Re : formule separer nom prenom

Jp14, merci pour ta macro.
Je viens de tester,
ça ne marche pas comme je veux :
je n'ai pas de civilité, et le nomprénom non séparés se trouve en colonne B de la feuille Feuil2

C@thy

Effectuez une recherche sur Excel Downloads...

formule separer nom prenom

C@thy

XLDnaute Barbatruc

Pièces jointes

Guest

Guest

C@thy

XLDnaute Barbatruc

Guest

Guest

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

C@thy

XLDnaute Barbatruc

C@thy

XLDnaute Barbatruc

BOISGONTIER

XLDnaute Barbatruc

Pièces jointes

C@thy

XLDnaute Barbatruc

JNP

XLDnaute Barbatruc

Pièces jointes

C@thy

XLDnaute Barbatruc

C@thy

XLDnaute Barbatruc

Pièces jointes

jp14

XLDnaute Barbatruc

C@thy

XLDnaute Barbatruc

C@thy

XLDnaute Barbatruc

Discussions similaires

Nous accordons de l'importance à votre vie privée