{"id":9344,"date":"2020-12-05T23:26:37","date_gmt":"2020-12-05T22:26:37","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=9344"},"modified":"2020-12-05T23:38:03","modified_gmt":"2020-12-05T22:38:03","slug":"excel-extraire-le-code-postal-et-la-ville-dune-adresse","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=9344","title":{"rendered":"Excel : extraire le code postal et la ville d\u2019une adresse"},"content":{"rendered":"<p style=\"text-align: justify;\">Avant d&#8217;<a href=\"https:\/\/www.auditsi.eu\/?tag=analyse-de-donnees\">analyser des donn\u00e9es<\/a>, il est g\u00e9n\u00e9ralement n\u00e9cessaire de les pr\u00e9parer. Cette \u00e9tape incontournable consiste \u00e0 nettoyer et formater les donn\u00e9es pour les mettre en condition d&#8217;\u00eatre analys\u00e9es selon les objectifs pr\u00e9vus.<\/p>\n<p style=\"text-align: justify;\">Par exemple, lorsque les adresses d&#8217;une liste d&#8217;entreprises sont stock\u00e9es dans un seul champ comprenant le num\u00e9ro, le nom de la rue, le code postal et la ville, l&#8217;utilisateur pourra souhaiter r\u00e9partir ces donn\u00e9es dans diff\u00e9rents champs : adresse, code postal et ville.<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9345\" rel=\"attachment wp-att-9345\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9345\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-1.png\" alt=\"Excel - Conversion Donn\u00e9es 1\" width=\"377\" height=\"322\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-1.png 377w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-1-300x256.png 300w\" sizes=\"auto, (max-width: 377px) 100vw, 377px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">A partir de la base ci-avant (classeur Excel), s\u00e9lectionner la colonne A. Dans le menu Donn\u00e9es, s\u00e9lectionner Convertir :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9346\" rel=\"attachment wp-att-9346\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9346\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-2.png\" alt=\"Excel - Conversion Donn\u00e9es 2\" width=\"651\" height=\"507\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-2.png 651w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-2-300x234.png 300w\" sizes=\"auto, (max-width: 651px) 100vw, 651px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">S\u00e9lectionner D\u00e9limit\u00e9 puis Suivant :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9347\" rel=\"attachment wp-att-9347\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9347\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-3.png\" alt=\"Excel - Conversion Donn\u00e9es 3\" width=\"507\" height=\"436\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-3.png 507w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-3-300x258.png 300w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">S\u00e9lectionner le s\u00e9parateur Virgule puis cliquer sur Terminer. C&#8217;est en effet une virgule qui s\u00e9pare le nom de la rue du reste de l&#8217;adresse dans cet exemple.<\/p>\n<p style=\"text-align: justify;\">L&#8217;adresse est maintenant r\u00e9partie sur deux colonnes : num\u00e9ros et rues d&#8217;un cot\u00e9 (colonne A) et codes postaux et villes de l&#8217;autre (colonne B).<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9348\" rel=\"attachment wp-att-9348\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9348\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-4.png\" alt=\"Excel - Conversion Donn\u00e9es 4\" width=\"531\" height=\"322\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-4.png 531w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-4-300x182.png 300w\" sizes=\"auto, (max-width: 531px) 100vw, 531px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Enfin, pour s\u00e9parer le code postal de la ville : s\u00e9lectionner la colonne B puis dans le menu Donn\u00e9es, s\u00e9lectionner l&#8217;option Convertir :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9349\" rel=\"attachment wp-att-9349\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9349\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-5.png\" alt=\"Excel - Conversion Donn\u00e9es 5\" width=\"613\" height=\"534\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-5.png 613w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-5-300x261.png 300w\" sizes=\"auto, (max-width: 613px) 100vw, 613px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Choisir l&#8217;option Largeur fixe puis cliquer sur Suivant :<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9350\" rel=\"attachment wp-att-9350\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9350\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-6.png\" alt=\"Excel - Conversion Donn\u00e9es 6\" width=\"507\" height=\"436\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-6.png 507w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-6-300x258.png 300w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Ins\u00e9rer un s\u00e9parateur entre les deux champs (double-clic) puis cliquer sur le bouton Suivant. Dans la fen\u00eatre qui appara\u00eet, choisir le format Texte pour le champ code postal puis cliquer sur Terminer. Le format Texte permet de conserver le z\u00e9ro \u00e0 gauche des codes postaux inf\u00e9rieurs \u00e0 10000.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9351\" rel=\"attachment wp-att-9351\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9351\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-7.png\" alt=\"Excel - Conversion Donn\u00e9es 7\" width=\"507\" height=\"436\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-7.png 507w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-7-300x258.png 300w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Apr\u00e8s ces deux traitements, les adresses sont maintenant r\u00e9parties en trois colonnes (champs de donn\u00e9es) :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=9352\" rel=\"attachment wp-att-9352\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-9352\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-8.png\" alt=\"Excel - Conversion Donn\u00e9es 8\" width=\"583\" height=\"323\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-8.png 583w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2020\/12\/Excel-Conversion-Donnees-8-300x166.png 300w\" sizes=\"auto, (max-width: 583px) 100vw, 583px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?glossary=excel\">Tout savoir sur Excel<\/a> \/ <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"https:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a>&nbsp;\/ En savoir plus sur l\u2019<a href=\"https:\/\/www.auditsi.eu\/?tag=analyse-de-donnees\">analyse de donn\u00e9es<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20extraire%20le%20code%20postal%20et%20la%20ville%20d%E2%80%99une%20adresse\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Avant d&#8217;analyser des donn\u00e9es, il est g\u00e9n\u00e9ralement n\u00e9cessaire de les pr\u00e9parer. Cette \u00e9tape incontournable consiste \u00e0 nettoyer et formater les donn\u00e9es pour les mettre en condition d&#8217;\u00eatre analys\u00e9es selon les objectifs pr\u00e9vus. Par exemple, lorsque les adresses d&#8217;une liste d&#8217;entreprises sont stock\u00e9es dans un seul champ comprenant le num\u00e9ro, le nom de la rue, le &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=9344\" class=\"more-link\">Continue reading &lsquo;Excel : extraire le code postal et la ville d\u2019une adresse&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20%3A%20extraire%20le%20code%20postal%20et%20la%20ville%20d%E2%80%99une%20adresse\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[48,1326],"tags":[2387,2073,105,2268,987,2388],"class_list":["post-9344","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","tag-adresse","tag-code-postal","tag-conversion-de-donnees","tag-menu-donnees","tag-separateur","tag-ville"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9344","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=9344"}],"version-history":[{"count":3,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9344\/revisions"}],"predecessor-version":[{"id":9355,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/9344\/revisions\/9355"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=9344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=9344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=9344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}