{"id":120,"date":"2012-02-13T17:29:03","date_gmt":"2012-02-13T16:29:03","guid":{"rendered":"http:\/\/www.psinke.nl\/wordpress\/?p=120"},"modified":"2015-02-03T16:57:45","modified_gmt":"2015-02-03T15:57:45","slug":"one-quick-way-to-convert-decimals-in-excel","status":"publish","type":"post","link":"https:\/\/www.psinke.nl\/blog\/one-quick-way-to-convert-decimals-in-excel\/","title":{"rendered":"One quick way to convert decimals in Excel"},"content":{"rendered":"<div class=\"afa2b3b851443650502c43e85c746c1f\" data-index=\"1\" style=\"float: none; margin:0px;\">\n<script type=\"text\/javascript\"><!--\r\ngoogle_ad_client = \"ca-pub-4223201538046844\";\r\n\/* standaardblok *\/\r\ngoogle_ad_slot = \"9522527129\";\r\ngoogle_ad_width = 320;\r\ngoogle_ad_height = 50;\r\n\/\/-->\r\n<\/script>\r\n<script type=\"text\/javascript\"\r\nsrc=\"http:\/\/pagead2.googlesyndication.com\/pagead\/show_ads.js\">\r\n<\/script>\n<\/div>\n<p>I frequently use the &#8220;text to columns&#8221; feature in MS Office 2007 and higher. Usually I paste csv or other delimited data in the first column, and use the Text to Column wizard to convert it to individual columns. It&#8217;s also very useful for converting decimals. Regional settings and the use of decimals can be a real pain, especially when importing data from various sources.\u00a0Usually the solution is to change decimal settings in Excel, or even in your operating system (regional settings). I do not understand why there is no feature in Office to interpret a field as a number regardless of the decimal separator, but fortunately there is a workaround.<\/p>\n<p>The case here is that the number value is not recognized correctly, for instance a field contains 24.67 and Excel thinks it&#8217;s not a number.<\/p>\n<p>The solution is to apply the <em>Text to column<\/em>s to the erroneous column again. See what happens.<\/p>\n<p>You can find the button here (I use Office for Mac, so\u00a0it might look different for you):<\/p>\n<p><a href=\"http:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-2.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-127 size-full\" title=\"text to columns button in excel\" src=\"http:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-2.png\" alt=\"\" width=\"301\" height=\"197\" srcset=\"https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-2.png 301w, https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-2-300x196.png 300w\" sizes=\"auto, (max-width: 301px) 100vw, 301px\" \/><\/a><\/p>\n<p>In the example below, the point is not recognized as a decimal point. Apparently, Excel accepts only comma&#8217;s here (might be the other way around in your case).<\/p>\n<div>\u00a0Select the column first.<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-126 size-full\" title=\"select column in excel\" src=\"http:\/\/www.psinke.nl\/wordpress\/wp-content\/uploads\/2012\/05\/excel-1.png\" alt=\"\" width=\"192\" height=\"240\" \/><\/p>\n<p>Click the Text to Columns button, then select the &#8220;delimited&#8221; option, click next. Click next again. You&#8217;ll see this page:<\/p>\n<p><a href=\"http:\/\/www.psinke.nl\/wordpress\/wp-content\/uploads\/2012\/05\/excel-3.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-128 size-full\" title=\"text to columns wizard excel\" src=\"http:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-3.png\" alt=\"\" width=\"535\" height=\"426\" srcset=\"https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-3.png 535w, https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-3-300x238.png 300w\" sizes=\"auto, (max-width: 535px) 100vw, 535px\" \/><\/a><\/p>\n<p>Now click &#8220;Advanced&#8230;&#8221;.<\/p>\n<p><a href=\"http:\/\/www.psinke.nl\/wordpress\/wp-content\/uploads\/2012\/05\/excel-4.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-129 size-full\" title=\"advanced text import settings excel text to columns\" src=\"http:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-4.png\" alt=\"\" width=\"377\" height=\"247\" srcset=\"https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-4.png 377w, https:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-4-300x196.png 300w\" sizes=\"auto, (max-width: 377px) 100vw, 377px\" \/><\/a><\/p>\n<p>Hah, I may choose a decimal and thousands separator. Change the decimal to a dot, and the thousands separator to a comma. Or again, the other way around. The trick is that you tell Excel here which character <em>is<\/em> a decimal, and not what it should be. A bit confusing, eh.<\/p>\n<p>Click OK to leave the wizard. And, tad-aah:<\/p>\n<p><a href=\"http:\/\/www.psinke.nl\/wordpress\/wp-content\/uploads\/2012\/05\/excel-6.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-130 size-full\" title=\"excel column decimals converted\" src=\"http:\/\/www.psinke.nl\/blog\/wp-content\/uploads\/2012\/05\/excel-6.png\" alt=\"\" width=\"198\" height=\"193\" \/><\/a><\/p>\n<p>These look like real numbers to me. Patrick vs. Excel: 1-0.<\/p>\n<p><span style=\"color: #ffffff;\">convert decimals in Excel<\/span><\/p>\n<!--CusAds0-->\n<div style=\"font-size: 0px; height: 0px; line-height: 0px; margin: 0; padding: 0; clear: both;\"><\/div>","protected":false},"excerpt":{"rendered":"<p>I frequently use the &#8220;text to columns&#8221; feature in MS Office 2007 and higher. Usually I paste csv or other delimited data in the first column, and use the Text to Column wizard to convert it to individual columns. It&#8217;s also very useful for converting decimals. Regional settings and the use of decimals can be&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,121],"tags":[128,166,64,164,124,165,122,275,14,125,123,126,127,129,117,118,163],"class_list":["post-120","post","type-post","status-publish","format-standard","hentry","category-dailylife","category-tips-and-tricks","tag-columns","tag-comma","tag-convert","tag-decimal-separator","tag-decimals","tag-dot","tag-excel","tag-figure","tag-mac","tag-ms-office","tag-numbers","tag-office","tag-text","tag-text-to-columns","tag-tips","tag-tricks","tag-xls"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/comments?post=120"}],"version-history":[{"count":5,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/120\/revisions"}],"predecessor-version":[{"id":443,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/posts\/120\/revisions\/443"}],"wp:attachment":[{"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/media?parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/categories?post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.psinke.nl\/blog\/wp-json\/wp\/v2\/tags?post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}