{"id":1271,"date":"2023-12-08T15:49:13","date_gmt":"2023-12-08T20:49:13","guid":{"rendered":"https:\/\/molecularsciences.org\/content\/?p=1271"},"modified":"2023-12-08T15:57:33","modified_gmt":"2023-12-08T20:57:33","slug":"cleaning-text-with-trim-and-proper-in-excel","status":"publish","type":"post","link":"https:\/\/molecularsciences.org\/content\/cleaning-text-with-trim-and-proper-in-excel\/","title":{"rendered":"Cleaning text with trim() and proper() in Excel"},"content":{"rendered":"\n<p>We often import data, including text, from different sources into Excel. Sometimes this text requires some cleaning such as removing spaces and adding consistency to the text. This post demonstrates how to use trim() and proper() functions to clean text in Excel.<\/p>\n\n\n\n<p>I have the following text that needs cleaning. It is a list of Canadian prime ministers. Note the spaces, use of capital or lower case letters in wrong places.<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"184\" height=\"485\" src=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-28.png\" alt=\"\" class=\"wp-image-1272\" srcset=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-28.png 184w, https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-28-114x300.png 114w\" sizes=\"auto, (max-width: 184px) 100vw, 184px\" \/><\/figure>\n\n\n\n<p>Following image show what happens when proper() function is applied e.g.\u00a0<strong>=proper(A2:A24)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"347\" height=\"483\" src=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-29.png\" alt=\"\" class=\"wp-image-1273\" srcset=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-29.png 347w, https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-29-216x300.png 216w\" sizes=\"auto, (max-width: 347px) 100vw, 347px\" \/><\/figure>\n\n\n\n<p>Note that \u201cSir john ABBOTT\u201d is reformatted as \u201cSir John Abbott\u201d and \u201cKIM campbell\u201d is reformatted as \u201cKim Campbell\u201d. Next, we apply trim() function e.g.\u00a0<strong>=trim(A2:A24)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"510\" height=\"485\" src=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-30.png\" alt=\"\" class=\"wp-image-1274\" srcset=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-30.png 510w, https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-30-300x285.png 300w\" sizes=\"auto, (max-width: 510px) 100vw, 510px\" \/><\/figure>\n\n\n\n<p>Note that trim() removed all the extra spaces. However, it does not reformat KIM to Kim or john to John. We can use both functions together to get both affects as follows:\u00a0<strong>=proper(trim(A2:A24)<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"671\" height=\"482\" src=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-31.png\" alt=\"\" class=\"wp-image-1275\" srcset=\"https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-31.png 671w, https:\/\/molecularsciences.org\/content\/wp-content\/uploads\/2023\/12\/image-31-300x215.png 300w\" sizes=\"auto, (max-width: 671px) 100vw, 671px\" \/><\/figure>\n\n\n\n<p>This does not seem very useful for this small list. However, imagine, you have a list of a 100,000 rows. Cleaning data is tedious work. I hope this helped you in your work.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We often import data, including text, from different sources into Excel. Sometimes this text requires some cleaning such as removing spaces and adding consistency to the text. This post demonstrates how to use trim() and proper() functions to clean text in Excel. I have the following text that needs cleaning. It is a list of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1278,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[207,369],"tags":[],"class_list":["post-1271","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","category-microsoft-office"],"_links":{"self":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1271","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/comments?post=1271"}],"version-history":[{"count":1,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1271\/revisions"}],"predecessor-version":[{"id":1276,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/posts\/1271\/revisions\/1276"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media\/1278"}],"wp:attachment":[{"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/media?parent=1271"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/categories?post=1271"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/molecularsciences.org\/content\/wp-json\/wp\/v2\/tags?post=1271"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}