{"id":8375,"date":"2019-10-13T01:04:56","date_gmt":"2019-10-12T23:04:56","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=8375"},"modified":"2021-01-05T22:49:30","modified_gmt":"2021-01-05T21:49:30","slug":"excel-afficher-les-codes-postaux-commencant-par-un-zero","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=8375","title":{"rendered":"Excel : afficher les codes postaux commen\u00e7ant par un z\u00e9ro"},"content":{"rendered":"<p style=\"text-align: justify;\">Excel n&#8217;affiche pas les z\u00e9ros non significatifs, qu&#8217;ils se trouvent sur la gauche d&#8217;un nombre (02100 est affich\u00e9 2100) ou sur la droite d&#8217;une d\u00e9cimale (10,50 est affich\u00e9 10,5).<\/p>\n<p style=\"text-align: justify;\">Appliqu\u00e9 aux codes postaux fran\u00e7ais, ce principe affiche les codes postaux inf\u00e9rieurs \u00e0 10000 sur 4 positions au lieu des 5 attendues.<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8376\" rel=\"attachment wp-att-8376\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8376\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-codes-postaux.png\" alt=\"Format codes postaux\" width=\"813\" height=\"133\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-codes-postaux.png 813w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-codes-postaux-300x49.png 300w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-codes-postaux-768x126.png 768w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-codes-postaux-730x119.png 730w\" sizes=\"auto, (max-width: 813px) 100vw, 813px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Heureusement, Excel propose des solutions.<\/p>\n<p style=\"text-align: justify;\"><em>1\u00e8re solution : fonction TEXTE<\/em><\/p>\n<p style=\"text-align: justify;\">La <em><a href=\"https:\/\/www.auditsi.eu\/?p=7569\">fonction TEXTE<\/a><\/em> est la fonction par excellence pour formater des donn\u00e9es. Pour formater les codes postaux sur cinq positions :<\/p>\n<p style=\"text-align: center;\">=TEXTE(A3;&#8221;00000&#8243;)<\/p>\n<p style=\"text-align: justify;\"><em>2\u00e8me solution : fonction NBCAR<\/em><\/p>\n<p style=\"text-align: justify;\">La <em>fonction NBCAR<\/em> renvoie le nombre de caract\u00e8res contenu dans une cha\u00eene de caract\u00e8res. Dans l&#8217;exemple qui suit si le code postal ne comprend que quatre caract\u00e8res (<em>NBCAR(A3)=4<\/em>), un z\u00e9ro est ajout\u00e9 (<em>&#8220;0&#8221;<\/em>) au code postal (<em>&amp;A3<\/em>) :<\/p>\n<p style=\"text-align: center;\">=SI(NBCAR(A3)=4;&#8221;0&#8243;;&#8221;&#8221;)&amp;A3<\/p>\n<p style=\"text-align: justify;\"><em>3\u00e8me solution : fonctions REPT et NBCAR<\/em><\/p>\n<p style=\"text-align: justify;\">La fonction <em>REPT<\/em> r\u00e9p\u00e8te x fois un caract\u00e8re. Dans cet exemple, la fonction <em>REPT<\/em> r\u00e9p\u00e8te &#8220;0&#8221; x fois (<em>5-NBCAR(A3)<\/em>) et l&#8217;ajoute&nbsp;au code postal (<em>&amp;A3<\/em>) :<\/p>\n<p style=\"text-align: center;\">=REPT(&#8220;0&#8221;;5-NBCAR(A3))&amp;A3<\/p>\n<p style=\"text-align: justify;\"><em>4\u00e8me solution : format Code postal<\/em><\/p>\n<p>Le format Code postal permet de formater l&#8217;affichage des donn\u00e9es (\u00e0 la diff\u00e9rence des solutions pr\u00e9c\u00e9dentes, les donn\u00e9es ne sont pas modifi\u00e9es) :<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?attachment_id=8377\" rel=\"attachment wp-att-8377\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-8377\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-code-postal.png\" alt=\"Format code postal\" width=\"564\" height=\"465\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-code-postal.png 564w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2019\/10\/Format-code-postal-300x247.png 300w\" sizes=\"auto, (max-width: 564px) 100vw, 564px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Cette m\u00eame fonctionnalit\u00e9 permet de formater l&#8217;affichage des num\u00e9ros de s\u00e9curit\u00e9 sociale et de t\u00e9l\u00e9phone.<\/p>\n<p style=\"text-align: justify;\">__<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <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><\/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%20afficher%20les%20codes%20postaux%20commen%C3%A7ant%20par%20un%20z%C3%A9ro\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>Excel n&#8217;affiche pas les z\u00e9ros non significatifs, qu&#8217;ils se trouvent sur la gauche d&#8217;un nombre (02100 est affich\u00e9 2100) ou sur la droite d&#8217;une d\u00e9cimale (10,50 est affich\u00e9 10,5).<\/p>\n<p>Appliqu\u00e9 aux codes postaux fran\u00e7ais, ce principe affiche les codes postaux inf\u00e9rieurs \u00e0 10000 sur 4 positions au lieu des 5 attendues.<\/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%20afficher%20les%20codes%20postaux%20commen%C3%A7ant%20par%20un%20z%C3%A9ro\";<\/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":[2073,2074,1393,518,2075,1540,1936],"class_list":["post-8375","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","tag-code-postal","tag-format-special","tag-nbcar","tag-numero-de-securite-sociale","tag-numero-de-telephone","tag-rept","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\/8375","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=8375"}],"version-history":[{"count":5,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8375\/revisions"}],"predecessor-version":[{"id":9461,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/8375\/revisions\/9461"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8375"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8375"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8375"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}