Il est fréquent de récupérer des tableurs où les adresses postales n’ont pas été correctement remplies. Toute l’adresse occupe une seule cellule alors qu’il faudrait qu’elle soit scindée en 3 colonnes : adresse, code postal et localité. Cette disposition est préférable car elle vous permettra d’exploiter aisément votre liste. Par exemple si vous souhaitez la trier, l’ordonner par ville, l’utiliser pour faire du publipostage et n’exploiter qu’une partie de la liste.

Avec un peu de méthode et d’entrainement il est très rapide et facile de réorganiser un tableau de la sorte.

Pour réaliser le tutoriel, je vous propose de télécharger ce fichier :

[qbutton size= »small » style= » » text= »Télécharger le fichier » icon_pack= »font_elegant » fa_icon= » » fe_icon= »icon_download » icon_color= » » link= »http://www.iouston.com/wp-content/uploads/2012/09/adresses_a_reorganiser.xlsx » target= »_blank » color= » » hover_color= » » border_color= » » hover_border_color= » » background_color= » » hover_background_color= » » font_style= » » font_weight= » » text_align= » » margin= » »]

 

Extraire une chaîne de caractères :

Pour réaliser notre réorganisation, il va nous falloir scinder en 3 l’adresse et extraire dans chaque colonne la partie qui nous intéresse. Pour cela nous utilisons la formule stxt. Elle permet d’extraire une chaîne de caractères dans un texte depuis un numéro de départ en précisant un nombre de caractères :

Dans la cellule B2 : =STXT(A2;1;10)

aura pour effet d’écrire les 10 premiers caractères depuis le premier de la cellule A2

Pour n’afficher que l’adresse quelque soit sa taille, il faut trouver un critère commun à toutes les adresses. Ici, ce critère est  » 5″ qui correspond à l’espace entre l’adresse et le code postal et le premier chiffre du code postal.

Renvoyer le nombre de caractères selon la place d’une chaine de caractères recherchée

La fonction : cherche, donne le nombre de caractères (de gauche à droite)  selon la place d’une chaîne de caractères recherchée. Par exemple, cherchons ou se trouve dans la première adresse en A2, le critère commun à toutes les adresses, qui est ici :  » 5″

 en B3 : =CHERCHE(" 5";a2)

cela va renvoyer 24. On peut donc désormais scinder notre adresse qui est en A2, du premier caractère, jusqu’à l’emplacement de notre critère commun :

 Dans la cellule B2 : =STXT(A2;1;CHERCHE(" 5";A2))

et nous obtenons alors uniquement l’adresse.

Obtenir le code postal

Pour obtenir le code postal, il nous suffit de supprimer l’adresse obtenue précédemment dans l’adresse globale. Cela nous renverra donc le code postal et la ville. Excel dispose de la fonction : substitue, qui permet de substituer un texte par un autre dans un texte. A noter que dans l’exemple, nous remplaçons le texte recherchée (l’adresse) par rien du tout, c’est à dire pour Excel, un texte vide, soit «  »

Dans la cellule C2 : =SUBSTITUE(A2;B2;"")

Comme on sait que le code postal correspond aux 5 premiers caractères de cette chaine, il est facile de l’extraire grâce à STXT donc :

Dans la cellule C2 : =STXT(SUBSTITUE(A2;B2;"");1;5)

renvoie le code postal

Obtenir la localité

Il suffit de faire la même chose pour la ville mais en indiquant un grand nombre de caractères (pour avoir la totalité de la localité) et de ne pas oublier l’espace entre le code postal et la ville.
ce qui donne :

Dans la cellule D2 : =STXT(SUBSTITUE(A2;B2;"");6;1000)

Voilà, nous avons maintenant notre première adresse scindée en trois colonnes, il ne nous reste plus qu’à utiliser la poignée de recopie pour afficher correctement notre liste.

Une erreur apparait…

Une erreur apparait car nous avons dans notre liste des adresses dont le code postal commence par 67. Notre critère commun  » 5″ ne peut donc pas fonctionner, excel renvoie une erreur. Dans le cas où les adresses de votre liste ne viennent pas du même département, il faut soit procéder par étape et travailler département par département, ce qui est fastidieux si la liste est longue ou si les adresses sont mélangées. Sinon on peut aussi utiliser une formule qui va extraire une suite de 5 chiffres à la suite : notre code postal. Cette fonction est beaucoup plus complexe et je ne vais pas en détailler son fonctionnement ici. Garder là dans un coin :

=STXT(A2;EQUIV(1;(ESTNUM(STXT(SUBSTITUE(A2;" ";"µ");LIGNE(INDIRECT("1:"&NBCAR(A2)));5)*10000))*1;0);5)

A noter que cette formule fait partie des formules matricielles d’excel et qu’il faut donc la valider avec ctrl+maj+entrée pour qu’Excel la prenne en compte, sans quoi, vous aurez une erreur. De même les formules matricielles demandent beaucoup d’effort à Excel, si votre liste est longue de plusieurs milliers d’adresses, travaillez en plusieurs fois. Attention, à bien rester vigilant, même avec cette fonction, il est possible d’avoir quelques erreurs. Cette formule recherche 5 chiffres à la suite et fait une extraction. Si une boite postal se compose de 5 chiffres, Excel risque d’extraire la mauvaise chaîne de caractères. Pensez donc à vérifier vos résultats et ne faites pas uniquement confiance à Excel.

A partir de cette formule et en vous aidant des formules vues précédemment, vous arriverez aisément à traiter n’importe quel tableau

Finir le travail : Coller en valeur

Une fois votre liste correctement réorganisée vous pouvez supprimer les adresses de la colonne A. Mais attention, toutes les formules font référence d’une manière ou d’une autre à cette colonne. Aussi, vous devez sélectionner les colonnes B,C,D puis copier et enfin collage spécial – coller en valeur. Excel supprime alors la formule des cellules concernée et n’y laisse que le résultat. Vos colonnes sont désormais bien organisée et complétement indépendante de la colonne A

 

fringilla ut Curabitur suscipit ut non dictum Nullam justo lectus