Extraction de contenu d'une colonne sur plusieurs colonnes

**Stanir** · 02/12/2009, 13h35

Bonjour à tous,

voici mon problème : j'ai un tableau contant dans une colonne le nom adresse d'entreprises; je dois extraire dans quatre colonnes le nom, l'adresse, le code postal et la ville. J'ai quelques idées mais je n'arrive pas à le rendre l'extraction plus automatique !

Merci de votre aide.

**Krovax** · 02/12/2009, 14h39

Bonjour,

Tu pourrais peut être nous expliquer tes idées non?

Si j'ai bien compris dans une colonne tu as
|Bob Morane 18 rue des vielle BD 33000 Bordeaux|
Et tu veux
|Bob Morane| 18 rue des vielles BD| 33000| Bordeaux|
Maintenant question comment excel est sensé distinguer le nom de la ville de la rue? Pour toi avec ton super cerveau c'est limite évident mais pour excel pas du tout.

Dans mon cas c'est jouable tu peux tester les chiffre pour le changement de colonne

regarde ce tuto :
la fonction split et pour tester les nombre isnumeric dans laide excel

Invité · 03/12/2009, 09h45

Bonjour

Merci de continuer sur cette discussion

Il faudrait que tu expliques comment est formatée ton adresse, en ligne ou avec un retour chariot à chaque changement d'informations ?

En effet, dans le premier cas, ce ne sera pas du tout facile, mais dans le secont peut-être un peu plus facile.

Philippe

**Stanir** · 03/12/2009, 09h59

Merci Krovax pour le tuto;

Je debute en VBA et je ne sais par quel bout commencer !

Invité · 03/12/2009, 10h03

Envoyé par Philippe JOCHMANS

Il faudrait que tu expliques comment est formatée ton adresse, en ligne ou avec un retour chariot à chaque changement d'informations ?

**Stanir** · 03/12/2009, 10h35

Le contenu de la cellule à extraire est de la forme :
NOM 254 avenue de la paix 13589 LES BAUMETTES

**lvr** · 03/12/2009, 11h09

La base de la solution est de déterminer la manière dont est formaté ton texte
Tu as plusieurs possibilités:
- Soit ton texte contient des séparateurs (comme l'exemple de Krovax), il faut faire un split sur ces séparateurs

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NOM|254|avenue de la paix|13589|LES BAUMETTES
NOMPPLUS|254|avenue des rouages|13590|LA BAULE

- Soit ton text contient des champs espacés de manière constante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NOM          254 avenue de la paix            13589 LES BAUMETTES
NOMPPLUS     254 avenue des rouages           13590 LA BAULE

Et là tu coupe de 0-9 le nom, de 10-19 le numéro, de 20-49 la rue, ...

- Soit tu n'as pas les 2 formatages de ci-dessus, mais qu'au minimum les éléments sont toujours placés dans le même ordre

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NOM 254 avenue de la paix 13589 LES BAUMETTES
NOMPPLUS 254 avenue des rouages 13590 LA BAULE

Ca devient plus difficile, car il va falloir détecter les différents éléments:
*du début au premier espace suivi d'un chiffre tu as le nom
*de là jusqu'au prochain espace tu as le numéro
*de là jusqu'à un nombre en 5 positions tu as l'adresse
*le nombre en cinq positions c'est le code postal
*de là jusqu'à la fin c'est la localité
Cette logique est plutôt compliqué à écrire en Excel.

- Si tu n'as aucun des 2 1ers formatages et que les éléments ne sont pas toujours placés dans le même ordre, alors cela devient carrément impossible.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
NOM 254 avenue de la paix 13589 LES BAUMETTES
NOMPPLUS avenue des rouages 254 LA BAULE 13590

Conclusion, essaye d'améliorer au maximum ta source pour tomber dans un des 2 1ers formatage.

Si pas possible, il va falloir faire du compliqué.

**Stanir** · 03/12/2009, 11h53

Oui je peux avoir des éléments sont toujours placés dans le même ordre, du type :

NOM 254 avenue de la paix 13589 LES BAUMETTES
NOMPPLUS 254 avenue des rouages 13590 LA BAULE

Mais je ne vois pas comment commencer (N'oublies pas je commence en VBA !)

Merci.

**lvr** · 03/12/2009, 12h38

Tu peux utiliser le VBA de trois manières ou le faire sans VBA:
1) pour définir de nouvelle fonction à mettre dans ta sheet, ex

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Function IncXX(rge as range) as Integer
   IncXX=rge.value+1
   end function

Et dans ta sheet, en A2 tu écris "=IncXX(A1)"

Avantage: c'est très souple à utiliser dans la sheet, car tu places et déplaces ta fonction comme tu veux.
Désavantage: une fonction n'a qu'un seul retour et que toi tu en voudrais 5 !

2) un code VBA static qui parcourt ta sheet ou une sélection et découpe tout.
Avantage: ça fait ce que tu veux et c'est pas trop compliqué
Désavantages: 1) c'est hyper static. Si tu décides de déplacer tes colonnes tu doit modifier tout ton VBA, 2) tu dois déclencher manuellement la macro quand tu veux qu'elle fasse le découpage

3) le même code qu'en (2) mais triggé par le fait que le contenu d'une cellule ait changé. Ca complique le code mais tu évites le déclenchage manuel.
Pour le trigger, va dans l'éditeur dans le module associé à ta feuille et dans les drop-down du dessus, sélectionne "Worksheet" à droite et "Change" à gauche. Tu es dans l'event "quelque chose a changé dans ma feuille"

4) sans VBA c'est aussi possible, rien qu'avec des formules, mais c'est compliqué

Maintenant fais ton marché ;-)

**Stanir** · 03/12/2009, 13h18

J'essaie la première methode pour voir !
Merci Lvr !

**Stanir** · 03/12/2009, 13h40

J'ai essayé la première methode, et quand j'écris "=IncXX(a1)" dans a2 excel repond avec #NOM?

???

**lvr** · 03/12/2009, 15h37

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

mon intrusion (et en présentant toutes mes excuses) dans cette discussion.

Au contraire, deux avis valent mieux qu'un. Et je suis entièrement d'accord avec toi. Si Stanir pouvait obtenir ses adresses en CSV ou en fixed-length, ce serait même un jeu d'enfant.
Pour son cas de figure, ton idée du dictionnaire je n'y avais pas pensé. Je note, au cas où moi aussi j'ai à faire face à ce genre de problème.

**EmmanuelleC** · 03/12/2009, 16h32

Tout en étant d'accord avec ucfoutu, comme je veux apprendre moi meme a manipuler des fonctions maisons, je t'ai fais 5 fonctions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Function nom(rge As Range)
 
nbrspc = Len(rge.Value) - Len(Replace(rge.Value, " ", ""))
esp = 1
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
    nom = Left(rge.Value, InStr(esp, rge.Value, " ") - 1)
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Function numero(rge As Range)
 
nbrspc = Len(rge.Value) - Len(Replace(rge.Value, " ", ""))
esp = 1
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
numero = var1
 
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Function rue(rge As Range)
 
nbrspc = Len(rge.Value) - Len(Replace(rge.Value, " ", ""))
esp = 1
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
debut = InStr(1, rge.Value, var1) + Len(var1) + 1
 
nbrspc = Len(Right(rge.Value, Len(rge.Value) - debut + 1)) - Len(Replace(Right(rge.Value, Len(rge.Value) - debut + 1), " ", ""))
esp = debut
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
    fin = InStr(esp, rge.Value, " ")
 
 rue = Mid(rge.Value, debut, fin - debut)
 
 
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Function codepostal(rge As Range)
 
nbrspc = Len(rge.Value) - Len(Replace(rge.Value, " ", ""))
esp = 1
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
debut = InStr(1, rge.Value, var1) + Len(var1) + 1
 
nbrspc = Len(Right(rge.Value, Len(rge.Value) - debut + 1)) - Len(Replace(Right(rge.Value, Len(rge.Value) - debut + 1), " ", ""))
esp = debut
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
    fin = InStr(esp, rge.Value, " ")
 
 codepostal = Val(Mid(rge.Value, fin + 1, fin + InStr(fin, rge.Value, "")))
 
 
End Function

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Function ville(rge As Range)
 
nbrspc = Len(rge.Value) - Len(Replace(rge.Value, " ", ""))
esp = 1
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
debut = InStr(1, rge.Value, var1) + Len(var1) + 1
 
nbrspc = Len(Right(rge.Value, Len(rge.Value) - debut + 1)) - Len(Replace(Right(rge.Value, Len(rge.Value) - debut + 1), " ", ""))
esp = debut
 
For i = 1 To nbrspc
    var1 = Val(Right(rge.Value, Len(rge.Value) - InStr(esp, rge.Value, " ")))
    If var1 <> 0 Then
    Exit For
    Else
    esp = InStr(esp, rge.Value, " ") + 1
    End If
Next i
 
    fin = InStr(esp, rge.Value, " ")
 
 cp = Val(Mid(rge.Value, fin + 1, fin + InStr(fin, rge.Value, "")))
 ville = Right(rge.Value, Len(rge.Value) - InStr(1, rge.Value, cp) - Len(cp))
End Function

C'est surement pas propre, mais ca fonctionne (jusqu'au moment ou une de lignes ne respecteras pas le modele que tu as indiqué)

Question a ceux qui s'y connaissent en vba, j'ai du reprendre chaque fois des bouts de codes de la fonction précédente et ca me semble ridicule.
Je ne peux pas utiliser dans une fonction avec un worksheetfunction. une des fonction que j'ai crée précédement?

**ucfoutu** · 03/12/2009, 18h14

Ce que tu cherches à faire, Emmanuelle, pourrait s'exprimer ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Private Sub CommandButton1_Click()
 Dim ch As String, ch1 As String, nom As String, numero As String, lettread As String, codepostal As String, ville As String
 ch = "NOM 254 avenue de la paix 13589 LES BAUMETTES"
 ch1 = ch
 nom = extraitlettres(ch1)
 numero = CStr(Val(ch1))
 ch1 = Mid(ch1, Len(numero) + 1)
 lettresad = extraitlettres(ch1)
 codepostal = CStr(Val(ch1))
 ville = Mid(ch1, Len(codepostal) + 1)
 MsgBox nom & vbCrLf & numero & vbCrLf & lettresad & vbCrLf & codepostal & vbCrLf & ville
End Sub
 
Private Function extraitlettres(ByRef ch1 As String) As String
  While Not IsNumeric(Left(ch1, 1))
    nom = nom & Left(ch1, 1)
    ch1 = Mid(ch1, 2)
  Wend
  extraitlettres = Trim(nom)
End Function

Sans préjudice (bien évidemment, de toutes les réserves que j'ai exprimées)

Si maintenant j'étais Stanir et que je lançais une telle moulinette en dépit de mes remarques et réserves, je prendrais AU MOINS les précautions que montre le code suivant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Private Sub CommandButton1_Click()
 Dim ch As String, ch1 As String, nom As String, numero As String, lettread As String, codepostal As String, ville As String
 ch = "DUPONT Jean 254 avenue de la paix 64600 ANGLET"
 ch1 = ch
 nom = extraitlettres(ch1)
 numero = CStr(Val(ch1))
 ch1 = Mid(ch1, Len(numero) + 1)
 lettresad = extraitlettres(ch1)
 codepostal = CStr(Val(ch1))
 ville = Trim(Mid(ch1, Len(codepostal) + 1))
 MsgBox nom & vbCrLf & numero & vbCrLf & lettresad & vbCrLf & codepostal & vbCrLf & ville
 MsgBox verif(nom, numero, lettresad, codepostal, ville)
End Sub
 
Private Function extraitlettres(ByRef ch1 As String) As String
  While Not IsNumeric(Left(ch1, 1))
    nom = nom & Left(ch1, 1)
    ch1 = Mid(ch1, 2)
  Wend
  extraitlettres = Trim(nom)
End Function
 
Private Function verif(ByVal nom, ByVal numero, lettresad, codepostal, ville) As String
  verif = "probablement bon..."
  If InStr(nom, " ") Then
    nom = Mid(nom, InStr(nom, " ") + 1)
    If InStr(nom, " ") Then verif = "doute sur le nom": Exit Function
  End If
  If Val(numero) > 999 Then verif = "doute sur le numéro ": Exit Function
  If Len(codepostal) > 5 Then verif = "doute / code postal et probablement sur l'adresse en général": Exit Function
  If Len(ville) > 20 Or InStr(ville, " ") Then verif = "doute / ville et probablement sur l'adresse en général)"
End Function

Pour tout dire, j'y ajouterais même un contrôle supplémentaire sur Lettresad en utilisant une comparaison du 1er mot de cette chaîne avec le "dictionnaire" que j'ai évoqué plus haut ... (c'est réalisable)

Ce ne seraient toutefois là que (j'insiste) des précautions minimum et en aucun cas une garantie (me relire à ce sujet).

EDIT :
Correction importante :
modifier :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

codepostal = CStr(Val(ch1))

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
codepostal = CStr(Val(ch1))
 If Len(codepostal) = 4 Then codepostal = Format(codepostal, "0####")

Ce qui aura un double effet :
1) code postal commençant par un 0 pris en charge
2) si plus de 5 chiffres au code postal ===>> la ville sera dénoncée (subtil) en erreur par le "jeu" (le traitement suivant) du reste ...

**Stanir** · 04/12/2009, 10h36

Bonjour vous deux, merci de participer à resoudre le pb; je n'ai pu suivre les discussions avant.

Je débute vraiment le VBA, jusqu'ici pour extraire je me sers des fonctions d'excel ( Cherche, STXT, etc) mais c'est trop long à mettre en oeuvre, c'est pour cela que je pense à faire du VB mais ce n'est pas gagné !

En tous les cas merci !

Extraction de contenu d'une colonne sur plusieurs colonnes

Macros et VBA Excel

Discussions similaires

Partager

Partager