{"id":4047,"date":"2016-08-02T17:29:16","date_gmt":"2016-08-02T15:29:16","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=4047"},"modified":"2023-11-30T21:22:27","modified_gmt":"2023-11-30T20:22:27","slug":"cleaning-data-excels-clean-formula","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/cleaning-data-excels-clean-formula\/","title":{"rendered":"Cleaning Data with Excel&#8217;s CLEAN Formula"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><em><strong>What does it do?<\/strong><\/em><\/p>\n<p>Removes\u00a0all nonprintable characters from text<\/p>\n<p><em><strong>Formula breakdown:<\/strong><\/em><\/p>\n<p>=CLEAN(<span style=\"color: #ff0000;\">text<\/span>)<\/p>\n<p><em><strong>What it means:<\/strong><\/em><\/p>\n<p>=CLEAN(<span style=\"color: #ff0000;\">this dirty text cell<\/span>)<\/p>\n<hr \/>\n<p>There are times when imported text from other applications\u00a0contain characters that are unprintable. The\u00a0<strong>CLEAN\u00a0<\/strong>formula in Excel can clean up the unprintable characters easily.<\/p>\n<p>This is how it is done below (go to the bottom of the page if you want to see the animated gif tutorial):<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/xPzimWSgr80?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"display: inline-block; width: 0px; overflow: hidden; line-height: 0;\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><\/p>\n<p><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle;\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><br \/>\n<a class=\"IRPP_minimalist icon-download\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean.xlsx\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Clean.xlsx<\/span><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong> <\/span>We need to <strong>enter the <em>Clean\u00a0<\/em>function<\/strong><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_57_1 counter-flat ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li><a href=\"#\" class=\"scroll-to-download\">Free Practice Workbook<\/a><\/li><li class='ez-toc-page-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/cleaning-data-excels-clean-formula\/#CLEAN\" title=\"=CLEAN\">=CLEAN<\/a><\/li><\/ul><\/nav><\/div>\n<h2 style=\"text-align: center;\"><span class=\"ez-toc-section\" id=\"CLEAN\"><\/span><span style=\"color: #0000ff;\">=CLEAN<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span>\u00a0The Clean\u00a0argument:<\/p>\n<p><strong>Which text do we want to clean the dirty characters from?<\/strong><\/p>\n<p>This is our data source:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-01.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Cleaning Data with Excel&#039;s CLEAN Formula\"  class=\"alignnone size-full wp-image-4052\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-01.png\" alt=\"Cleaning Data with Excel&#039;s CLEAN Formula\" width=\"465\" height=\"173\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-01.png 465w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-01-300x112.png 300w\" sizes=\"(max-width: 465px) 100vw, 465px\" \/><\/a><\/p>\n<p><strong>Reference the cell which has the dirty data:<\/strong><\/p>\n<h2 style=\"text-align: center;\">=CLEAN<span style=\"color: #0000ff;\">(C9)<\/span><\/h2>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-02.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Cleaning Data with Excel&#039;s CLEAN Formula\"  class=\"alignnone size-full wp-image-4053\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-02.png\" alt=\"Cleaning Data with Excel&#039;s CLEAN Formula\" width=\"472\" height=\"178\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-02.png 472w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-02-300x113.png 300w\" sizes=\"(max-width: 472px) 100vw, 472px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong> <\/span>Do the same for the rest of the cells by using the\u00a0<strong>CLEAN\u00a0<\/strong>formula, notice all of the unprintable characters (Wingdings)\u00a0have been cleaned:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-03.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Cleaning Data with Excel&#039;s CLEAN Formula\"  class=\"alignnone size-full wp-image-4054\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-03.png\" alt=\"Cleaning Data with Excel&#039;s CLEAN Formula\" width=\"468\" height=\"181\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-03.png 468w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/07\/Clean-03-300x116.png 300w\" sizes=\"(max-width: 468px) 100vw, 468px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; What does it do? Removes\u00a0all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications\u00a0contain characters that are unprintable. The\u00a0CLEAN\u00a0formula in Excel can clean up the unprintable characters easily. This is how it is done below (go to the bottom of [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":17455,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,298,14],"tags":[163,142,70],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4047"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=4047"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/4047\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17455"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=4047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=4047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=4047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}