{"id":8401,"date":"2019-10-27T13:14:34","date_gmt":"2019-10-27T12:14:34","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=8401"},"modified":"2019-10-27T17:23:58","modified_gmt":"2019-10-27T16:23:58","slug":"excel-extraire-le-numero-de-departement-dun-code-postal-francais","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=8401","title":{"rendered":"EXCEL : Extraire le num\u00e9ro de d\u00e9partement d\u2019un code postal fran\u00e7ais"},"content":{"rendered":"<p style=\"text-align: justify;\">Les deux premiers chiffres d\u2019un code postal fran\u00e7ais d\u00e9signent le d\u00e9partement. Pour lire le num\u00e9ro du d\u00e9partement \u00e0 partir d\u2019Excel, il suffit d\u2019utiliser la fonction <em>GAUCHE(cha\u00eene ; Longueur)<\/em> ou <em>Left(Cha\u00eene,Longueur)<\/em> en VBA.<\/p>\n<p style=\"text-align: justify;\">La formule suivante lit le num\u00e9ro de d\u00e9partement&nbsp;d\u2019un code postal situ\u00e9 dans la cellule A1 :<\/p>\n<p style=\"text-align: center;\">=GAUCHE(A1&nbsp;; 2)<\/p>\n<p style=\"text-align: justify;\">Excel n&#8217;affiche pas les z\u00e9ros non significatifs. De fait, les <a href=\"https:\/\/www.auditsi.eu\/?p=8375\">codes postaux commen\u00e7ant par un z\u00e9ro<\/a> ne sont pas trait\u00e9s correctement par Excel. Il est donc n\u00e9cessaire d\u2019amender la formule pour tenir compte de cette difficult\u00e9&nbsp;:<\/p>\n<p style=\"text-align: center;\">=GAUCHE(TEXTE(A1;\u201d00000\u2033);2)<\/p>\n<p style=\"text-align: justify;\">Le r\u00f4le de <em>TEXTE(A1;&#8221;00000&#8243;)<\/em> est de forcer Excel de formater le code postal sur cinq positions y compris s&#8217;il commence par un z\u00e9ro.<\/p>\n<p style=\"text-align: justify;\">Les codes postaux corses commencent par 20 mais cette r\u00e9gion est divis\u00e9e en deux d\u00e9partements&nbsp;: 2A (Corse-du-Sud) et 2B (Haute-Corse). Il n\u2019y a pas de m\u00e9thode simple dans Excel pour ressortir les deux codes d\u00e9partements 2A et 2B.<\/p>\n<p style=\"text-align: justify;\">Les codes postaux des DOM et des TOM commencent par respectivement par 97 et par 98 et sont compos\u00e9s de 3 chiffres :<\/p>\n<ul>\n<li style=\"text-align: justify;\">D\u00e9partements d&#8217;Outre-Mer\n<ul>\n<li style=\"text-align: justify;\">971 Guadeloupe<\/li>\n<li style=\"text-align: justify;\">972 Martinique<\/li>\n<li style=\"text-align: justify;\">973 Guyane<\/li>\n<li style=\"text-align: justify;\">974 La R\u00e9union<\/li>\n<li style=\"text-align: justify;\">975 St-Pierre-et-Miquelon<\/li>\n<li style=\"text-align: justify;\">976 Mayotte<\/li>\n<\/ul>\n<\/li>\n<li style=\"text-align: justify;\">Territoires d&#8217;Outre-Mer\n<ul>\n<li style=\"text-align: justify;\">984 Terres-Australes et Antarctiques<\/li>\n<li style=\"text-align: justify;\">987 Polyn\u00e9sie Fran\u00e7aise<\/li>\n<li style=\"text-align: justify;\">986 Wallis-et-Futuna<\/li>\n<li style=\"text-align: justify;\">988 Nouvelle-Cal\u00e9donie<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">A noter, bien que ne repr\u00e9sentant pas une commune fran\u00e7aise, la Principaut\u00e9 de Monaco porte le code postal 98000 dans les \u00e9changes postaux.<\/p>\n<p style=\"text-align: justify;\">Afin de traiter les codes postaux des DOM\/TOM correctement, la formule est r\u00e9dig\u00e9e ainsi&nbsp;:<\/p>\n<p style=\"text-align: center;\">=GAUCHE(TEXTE(A1;&#8221;00000&#8243;);SI(OU(GAUCHE(TEXTE(A1;&#8221;00000&#8243;);2)=&#8221;97&#8243;;GAUCHE(TEXTE(A1;&#8221;00000&#8243;);2)=&#8221;98&#8243;);3;2))<\/p>\n<p style=\"text-align: justify;\">Cette nouvelle formule indique que si le code postal sur cinq positions (<em>TEXTE(A1;&#8221;00000&#8243;)<\/em>) commence par 97 ou 98, le num\u00e9ro de d\u00e9partement sera lu sur 3 positions (<em>;3<\/em>) sinon sur 2 (<em>;2<\/em>).<\/p>\n<p style=\"text-align: justify;\">A partir du code d\u00e9partement, il est possible d\u2019obtenir son nom avec la fonction <em>RECHERCHEV<\/em> pointant sur une liste des d\u00e9partements.<\/p>\n<p style=\"text-align: justify;\">En savoir plus&nbsp;: <a href=\"https:\/\/www.data.gouv.fr\/fr\/datasets\/base-officielle-des-codes-postaux\/\" target=\"_blank\" rel=\"noopener noreferrer\">Base officielle des codes postaux fran\u00e7ais<\/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%20num%C3%A9ro%20de%20d%C3%A9partement%20d%E2%80%99un%20code%20postal%20fran%C3%A7ais\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Les deux premiers chiffres d\u2019un code postal fran\u00e7ais d\u00e9signent le d\u00e9partement. Pour lire le num\u00e9ro du d\u00e9partement \u00e0 partir d\u2019Excel, il suffit d\u2019utiliser la fonction GAUCHE(cha\u00eene ; Longueur) ou Left(Cha\u00eene,Longueur) en VBA. La formule suivante lit le num\u00e9ro de d\u00e9partement&nbsp;d\u2019un code postal situ\u00e9 dans la cellule A1 : =GAUCHE(A1&nbsp;; 2) Excel n&#8217;affiche pas les z\u00e9ros &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=8401\" class=\"more-link\">Continue reading &lsquo;EXCEL : Extraire le num\u00e9ro de d\u00e9partement d\u2019un code postal fran\u00e7ais&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%20num%C3%A9ro%20de%20d%C3%A9partement%20d%E2%80%99un%20code%20postal%20fran%C3%A7ais\";<\/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":[7,48],"tags":[2073,1387,1936],"class_list":["post-8401","post","type-post","status-publish","format-standard","hentry","category-breves","category-excel","tag-code-postal","tag-gauche","tag-texte"],"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\/8401","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=8401"}],"version-history":[{"count":6,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8401\/revisions"}],"predecessor-version":[{"id":8407,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8401\/revisions\/8407"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8401"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8401"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}