{"id":4107,"date":"2018-11-14T11:44:21","date_gmt":"2018-11-14T16:44:21","guid":{"rendered":"http:\/\/lisa.rushworth.us\/?p=4107"},"modified":"2018-12-19T11:47:19","modified_gmt":"2018-12-19T16:47:19","slug":"did-you-know-excel-can-change-the-case-of-text%ef%bb%bf","status":"publish","type":"post","link":"https:\/\/www.rushworth.us\/lisa\/?p=4107","title":{"rendered":"Did you know \u2026 Excel can change the case of text?\ufeff"},"content":{"rendered":"\n<p>But before we go there, did you know you can have Excel show your formulae instead of data? It\u2019s great for showing how you\u2019ve done something in Excel \ud83d\ude0a On the \u201cFormulas\u201d ribbon bar, click \u201cShow Formulas\u201d. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"134\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase01-1024x134.png\" alt=\"\" class=\"wp-image-4108\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase01-1024x134.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase01-300x39.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase01-768x101.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase01.png 1363w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>I get information with all sorts of strange caPitAlizaTioN \u2013often the \u201cI didn\u2019t know I had my caps lock on when I hit caps lock to give you all upper case\u201d lISA RUSHWORTH casing. Or the \u201cI didn\u2019t bother to hit shift so it\u2019s all in lower case\u201d lisa rushworth. And even the \u201cI had caps lock on when I fixed something, but the rest is in lower case\u201d lisa rusHWorth. <\/p>\n\n\n\n<p>It\u2019s not aesthetically pleasing, but some systems have case sensitive sorting \u2013 Sally is alphabetically <em>before\u00a0<\/em>lisa. Some systems use case sensitive strings to collate \u2013 and \u201clisa rushworth\u201dand \u201cLisa Rushworth\u201d are two distinct records. And have I mentioned it\u2019s not aesthetically pleasing?<\/p>\n\n\n\n<p>But I\u2019ve got an Excel formula that will create <em>consistently<\/em> capitalized data. <\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"169\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase02-1024x169.png\" alt=\"\" class=\"wp-image-4109\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase02-1024x169.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase02-300x50.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase02-768x127.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase02.png 1186w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>See:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"166\" src=\"http:\/\/lisa.rushworth.us\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase03-1024x166.png\" alt=\"\" class=\"wp-image-4110\" srcset=\"https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase03-1024x166.png 1024w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase03-300x49.png 300w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase03-768x125.png 768w, https:\/\/www.rushworth.us\/lisa\/wp-content\/uploads\/2018\/12\/Excel-ConvertCase03.png 1201w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>In addition to using LOWER(), UPPER(), and PROPER(), you can combine these with LEFT() and RIGHT() to convert different parts of the string to different cases \u2013 as in my example of setting <em>just<\/em> the first letter capital. <\/p>\n\n\n\n<p>Left(A14,1) takes the first character from the left of the value in A14. LEN(A14)-1 is the length of the value in A14 minus one.RIGHT(A14(LEN(A14)-1)) then takes one less than the entire string length <em>from the right<\/em> of A14 (i.e. everything <em>except<\/em> the first character). <\/p>\n","protected":false},"excerpt":{"rendered":"<p>But before we go there, did you know you can have Excel show your formulae instead of data? It\u2019s great for showing how you\u2019ve done something in Excel \ud83d\ude0a On the \u201cFormulas\u201d ribbon bar, click \u201cShow Formulas\u201d. I get information with all sorts of strange caPitAlizaTioN \u2013often the \u201cI didn\u2019t know I had my caps &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[677],"tags":[],"class_list":["post-4107","post","type-post","status-publish","format-standard","hentry","category-office-365"],"_links":{"self":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4107","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=4107"}],"version-history":[{"count":1,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4107\/revisions"}],"predecessor-version":[{"id":4111,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=\/wp\/v2\/posts\/4107\/revisions\/4111"}],"wp:attachment":[{"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4107"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4107"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rushworth.us\/lisa\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4107"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}