Separer du texte dans une cellule

[lematou]

Bonjour à tous,

J'ai un fichier avec une colonne (A:A) qui contient 2134 adresses
Ex : 35 BD DE SUISSES
ou 123 Chemin du coin de la Moure
Ou 2568 Rue Matabiau
Je voudrais que cela se retrouve sous la forme : SUISSE (Boulevard de) MATABIAU (Rue)
COIN DE LA MOURE (Chemin du)
Je n'ai pas besoin des numéros mais seulement de la rue.
J'ai utilisé CTRL+ H pour remplacer les signes parasites
J'ai utilisé >donneés > Convertir
J'ai utilisé DROITE(A1;NBCAR(A1)- TROUVE(" ";A1;1)) pour extraire les N°
Mais je cherche une methode qui s'adapte à tous mes fichiers reçus.
Ils ont tous cette forme.
J'ai excel 2003 et xp Home
Y a t'il une solution en formules ou en vba.
Je cherche depuis plusieurs jours mais je patauge et je finis par le faire presque à la main.
Merci pour toute aide apportée. Je joins un morceau de fichier.

 

[flyonets44]

Re : Separer du texte dans une cellule

Bonsoir
voir ci dessous le code vba d'une solution fournie en 08 2010
sur ce forum
Cordialement
flyonets

Sub a()
Dim Plg As Range, c As Range, str$
With CreateObject("VBscript.RegExp")
.Global = True
.Pattern = "\d*(\.\d+)?"
Set Plg = ActiveSheet.Range([A1], [A65536].End(xlUp))
For Each c In Plg
str = .Execute(c.Text)(0)
c.Offset(, 1) = _
.Replace(c.Text, ""): c = str
Next c
End With
Set Plg = Nothing
End Sub

 

[néné06]

Re : Separer du texte dans une cellule

Bonsoir à tous

Une petite approche à arranger

A+

 

[laurent950]

Re : Separer du texte dans une cellule

Bonjour lematou, le forum.

J’ai fait une routine qui me semble correcte. En espèrent que cela réponde a vos besoins.


à La place du elseif j'aurais pu essayer avec un select case.

Peut être sur le forum il y a des personnes qui peuvent traité cela sous forme de tableau, pour ma part je ne sais pas encore faire cela.

J'ai testé cette macro fonctionne bien, je peux la commenter si vous avez besoin.


Sub test()

x = Range("a65536").End(xlUp).Row

Range(Cells(2, 1), Cells(x, 1)).Value = Application.Substitute(Range(Cells(2, 1), Cells(x, 1)), ",", "")
Range(Cells(2, 1), Cells(x, 1)).Value = Application.Trim(Range(Cells(2, 1), Cells(x, 1)))

For Each c In Range(Cells(2, 1), Cells(x, 1))
BD = "BD"
RUE = "RUE"
CHEMIN = "CHEMIN"
AVENUE = "AVENUE"
IMPASSE = "IMPASSE"

If c.Value Like ("* " & BD & " *") Then
c.Value = Replace(c.Value, BD, "")
c.Value = c.Value & " (Boulevard de)"

ElseIf c.Value Like ("* " & RUE & " *") Then
c.Value = Replace(c.Value, RUE, "")
c.Value = c.Value & " (Rue)"

ElseIf c.Value Like ("* " & CHEMIN & " *") Then
c.Value = Replace(c.Value, CHEMIN, "")
c.Value = c.Value & " (Chemin du)"

ElseIf c.Value Like ("* " & AVENUE & " *") Then
c.Value = Replace(c.Value, AVENUE, "")
c.Value = c.Value & " (Avenue)"

ElseIf c.Value Like ("* " & IMPASSE & " *") Then
c.Value = Replace(c.Value, IMPASSE, "")
c.Value = c.Value & " (Impasse)"

End If

For i = 1 To Len(c.Value)
If IsNumeric(Mid(c.Value, i, 1)) Then
Nombre = Val(Mid(c.value, i, Len(c.Value) - i + 1))
c.Value = Replace(c.Value, Nombre, "")
End If
Next i

Range(Cells(2, 1), Cells(x, 1)).Value = Application.Trim(Range(Cells(2, 1), Cells(x, 1)))

Next c

End Sub


Au plaisir de vous lire et d’y apporter des correctif si besoins

Cordialement
laurent

 

[lematou]

Re : Separer du texte dans une cellule

Bonjour à tous
Merci à flyonets44 c’est super comme macro. et ça marche pour toutes les rues sauf celles qui sont justement des nombre comme la rue 1814 ou la ru du 10 Avril. Mais je peux corriger à la main avant, il n’y en a que 24 sur 2134. Mais il ne faut surtout pas cliquer deux fois sinon je perds mes adresses. Faut dire que je ne suis pas brillant pour corriger…

Merci aussi à Néné06 c’est super. la macro marche sur presque toutes les rues sauf sur celles qui ont des chiffres en fin de cellule comme : 17 rue Jean AICARD APPT 171, ou , 5 rue le dormeur APT 144.
Là ça bugue à: Do Until Cells(i, 1) = ""
x = Len(Cells(i, 3))
Cells(i, 4) = Cells(i, 2) + Left(Cells(i, 3), x - Len(Cells(i, 2))) + ")"

ET UN grand merci aussi à Laurent950 . Cette macro réecrit dans la même cellule. Il faut que je nettoie à la main ( avant de la lancer) les mot parasites comme : BIS ,TER, APPT . Je crois que je ne peux pas y échapper. par contre peut-on mettre les Du ,DE , DE la, Allé dans la parenthèse
Ex : CARAVELLE (Impasse de la), COLONNE (Avenue de la) , PLANA (Rue Louis)

Je suis partant pour que tu (vous) me commentiez cette macro car je suis un débutant.
Merci beaucoup
Cordialement

Je joins un fichier à LAURENT et aux autres aussi bien sûr avec des rues difficiles pour moi.

 

[laurent950]

Re : Separer du texte dans une cellule

Bonjour lematou, le forum

J’ai fait le nettoyage pour cela.
BIS ,TER, APPT

par contre peut-on mettre les Du ,DE , DE la, Allé dans la parenthèse
Ex : CARAVELLE (Impasse de la), COLONNE (Avenue de la) , PLANA (Rue Louis)

il y a beaucoup de condition est il faut trouvé un algorithme cohérent j’ai fais une extraction, cela peux se faire en plusieurs passage.

Je pense que les premières choses à faire c’est connaître la règle pour travailler en base de données.

Une fois c’est BD ou BOULEVARD, peut ton aussi rencontré les mêmes choses en minuscule bd ou boulevard ?

Si je mes par exemple : Rue de l’art de Paris (c’est un exemple) mais sur la premières macro il va considérer qu’il y a deux fois DE et la restitution sera le suivant exemple : L’art Paris (Rue de de)

La difficulté est la en faite pour le nettoyage cela est faisable.

Vous pouvez regarder est essayer de trouver une règle, ou m’expliquer la provenance des données si elles ne peuvent pas être bordé.

Je ne suis pas loin de la solution mais il me manque des éléments

Je vous remercie

laurent

 

[lematou]

Re : Separer du texte dans une cellule

Merci Laurent,
En effet les adresses sont pleines de scories : APT, APPT, BAT-C, BAT, VILLA…..Je vais nomenclaturer tous ces mots ou nombres en trop Dans le début de ma macro.
Ensuite je vais remplacer toutes les sortes d’écritures des Rues, comme BD = Boulevard, IMP ou imp = Impasse, R. = Rue…etc.
Et pour finir j’utiliserai celle que j’ai remaniée un peu.
Les adresses viennent de différents lieux et sont une extraction .csv mais chaque personne à mis ce qu’elle voulait, il n’y a pas de blocage ni de validation prépersonnalisée. C’est là mon problème.
Je ne sais pas ce que veut dire « Border ?
Merci à tous j’ai remarqué qu’en utilisant vos trois macro adaptées à la suite j’arrive à quelque chose d’approchant.

 

[laurent950]

Re : Separer du texte dans une cellule

Bonsoir lematou,

J’ai travaillé sur ton projet est je suis resté sur ma première idée, impossible de faire une macro sur un fichier Excel non définie.

Lorsque je parle de bordé c'est-à-dire comme un gabarie pour faire rentré les informations dans des colonnes pour que cela soit rentré correctement.

J’ai fait la chose suivante et peut être cela pourra vous servir comme idées.

J’ai récupérer votre base de données sur la feuille Sauvegarde origine j’ai copié les donner est coller manuellement sur une nouvelle feuille Adresse découpage.

De la j’ai fait un travail (en macro que je vais vous expliquer)

Dans cette base copier, suppression des virgule par rien, puis suppression des espace en trop dans le texte (car pour l’ordinateur un espace = 1 caractère)

La suite :

Faire un découpage de la désignation : et je range chaque mot dans une case soit pour

Rue de metz = je range dans chaque cellule en ligne (Rue = c2) ; (de = d2) ; (metz = e2)

Bon cela pour chacune des désignations. Ce qui me donne un grand tableau = C2 :I1264

Je vais ensuite me servir de se tableau pour en extraire les doublons sans tenir comptes des valeurs numérique = effacer toute valeur numérique (l’astuce vous verrais dans le deuxième tableau a côté plusieurs fois BIS d’où il y a une valeur numérique coller a BIS. Colonne N:N par rapport à la colone T:T

Faite un filtre sur la colonne C:C est choisir filtre personnaliser avec le choix contient BIS vous verrais

54BIS = cellule c570
30BIS = cellule C899
28BIS = cellule c1832

Bon je poursuit pour pas perdre le fils est que cette astuce me servent par la suite, je fais encore un autre tableau a côté pour pas perdre l’historique et je recommence cette fois l’extraction sans doublon du tableau 2 pour en faire un troisième tableau

Sur ce dernier tableau il y aura toutes les valeurs sans doublon. A partir de cela ont peux faire une synthèse et une analyse du travail des personnes pour comprendre les erreurs faite et qui ne seront plus à faire, et pour faire le nettoyage c’est plus simple.

Il faut repérer les cas particulier, est pas tenir compte d’un l (seule dans une cellule) car c’est le séparateur espace que j’ai pris pour les mettre dans chaque cellule donc (l equipe) comme dans la base les apostrophe n’existe pas cela donne (l) dans une case est (équipe dans une autre) et comme j’ai trié (cela n’est plus en face) seul les cas comme (AV pour AVENUE) etc. sont intéressant pour l’analyse.

PS : si vous êtes responsable est vous gérer des personnes je pense le mieux c’est faire un usurforme est bien cadrer les champs à remplir (avec pour chacun un identifiant unique pour repérée les personnes qui ont fait l’erreur, pas pour sanctionner mais pour les correctifs a apporter et un gain de productivité)

Tenner moi au courant de votre macro et de se que vous avez choisie pour finaliser cette tache. Si vous avez besoin d’idées, j’ai une très grandes expérience dans se domaine

Au plaisir de regarder votre macro et vous aider

Ps : regarder le fichier est la fameuse feuille cela peux vous aider. Car chaque cas est reperé sur votre grande liste.

Lancer la macro doublon pour résultat, le fichier était trop gros avec les écritures

laurent

 

[Softmama]

Re : Separer du texte dans une cellule

Bonjour à tous,

ma contribution, ici :

Sub test()
Dim c As Range, t As Integer, vVoies As Variant
vVoies = Array("BD", "RUE", "IMPASSE", "CHEMIN", "AVENUE")
 Set c = [A2]
 Do While c <> ""
    For t = LBound(vVoies) To UBound(vVoies)
        Rép c, vVoies(t)
    Next t
    Set c = c(2, 1)
 Loop
 End Sub

Sub Rép(cc As Range, Voie As Variant)
    If InStr(cc, " " & Voie & " DE LA ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DE LA ") + Len(Voie) + 8) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " de la)"
    ElseIf InStr(cc, " " & Voie & " DES ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DES ") + Len(Voie) + 6) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " des)"
    ElseIf InStr(cc, " " & Voie & " DU ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DU ") + Len(Voie) + 5) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " du)"
    ElseIf InStr(cc, " " & Voie & " DE ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DE ") + Len(Voie) + 5) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " de)"
    ElseIf InStr(cc, " " & Voie & " D ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " D ") + Len(Voie) + 4) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " d')"
    ElseIf InStr(cc, " " & Voie & " ") > 0 Then
      cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " ") + Len(Voie) + 2) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & ")"
    End If
    If cc(1, 2) = "" Then cc(1, 2) = cc
End Sub

cf. fichier

 

[lematou]

Re : Separer du texte dans une cellule

Bonsoir à tous,
Je n'ai pas le contrôle sur les gens qui inscrivent leurs adresses. Mais je me suis fait une macro pour sortir tous les Mots et nombres parasites :BIS TER AAPPT APT ainsi que les lettres seules comme : BAT C . J'ai remis les apostrophes àla lettre L " L'ECLUSE ". puis avec "Trim j'enlève les espaces du début et de fin . ET en utilisant vos trois macros j'arrive à obtenir Un très bon résultat.
Je peux avoir soit : AVENUE DE LA COLONNE ou bien si je veux utiliser celle de Laurent950, j'obtiens COLONNE (Avenue de). Mais pour cela il faut que j'ai fait le ménage en entier.
Bien sûr j'aurais surement des surprises tellement il y a d'erreurs dans les adresses, mais cela va marcher. Puisque ça marche déjà avec 2137 adresses.
Merci Laurent 950 tu m'aides beaucoup. et Softmama, ta macro est impressionnante.
Dès que j'ai fini de coder proprement je joins ma solution ( je ne suis pas un pro) dès fois que cela serve!!! Avec des adresses difficiles comme 17 Chemin de l allier APt- 175 Hall 4
A très bientôt