{"id":2436,"date":"2016-02-26T17:40:19","date_gmt":"2016-02-26T16:40:19","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2436"},"modified":"2023-11-30T21:49:07","modified_gmt":"2023-11-30T20:49:07","slug":"turn-text-to-values-with-excel-paste-special-values","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/turn-text-to-values-with-excel-paste-special-values\/","title":{"rendered":"Turn Text To Values With Paste Special Values"},"content":{"rendered":"<p>Many times you would have received data from your IT system which is formatted wrong! \u00a0Well a gazillion times if you work in a mid sized firm \ud83d\ude42<\/p>\n<p>When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as\u00a0text rather than a value.<\/p>\n<p><strong><span style=\"color: #ff0000;\">Tip 1:<\/span><\/strong> You can press <strong>F2<\/strong> in the cell to see why it is in text format.<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/f2-cell.png\" rel=\"attachment wp-att-2437\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-2437\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/f2-cell.png\" alt=\"Turn Text To Values With Paste Special Values\" width=\"165\" height=\"198\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">Tip 2:<\/span><\/strong>\u00a0You can also press <strong>CTRL+1<\/strong>\u00a0(which brings up the Format Cells dialogue box) to confirm that it is in text:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/format-cells.png\" rel=\"attachment wp-att-2438\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-2438\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/format-cells.png\" alt=\"Turn Text To Values With Paste Special Values\" width=\"525\" height=\"460\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/format-cells.png 525w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/format-cells-300x263.png 300w\" sizes=\"(max-width: 525px) 100vw, 525px\" \/><\/a><\/p>\n<p><strong><span style=\"color: #ff0000;\">Tip 3:\u00a0<\/span><\/strong><span style=\"color: #ff0000;\"><span style=\"color: #000000;\">You can also use the <strong>ISTEXT<\/strong> function to confirm a cell`s format:<\/span><\/span><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/istext.png\" rel=\"attachment wp-att-2439\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-2439\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/istext.png\" alt=\"Turn Text To Values With Paste Special Values\" width=\"279\" height=\"105\" \/><\/a><\/p>\n<p>Now you can easily convert the text into values by using the\u00a0<em><strong>Paste Special &gt; Values &gt; Multiply<\/strong><\/em> combination. \u00a0Here is how&#8230;<br \/>\n<iframe src=\"https:\/\/www.youtube.com\/embed\/9qbUl-JL6ic?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<span style=\"text-decoration: underline; color: #0000ff;\"><a class=\"IRPP_minimalist icon-download\" style=\"color: #0000ff; text-decoration: underline;\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/Turn-text-to-values.xlsx\" rel=\"\" target=\"_blank\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<\/span><span class=\"postTitle px-3\">Turn-text-to-values.xlsx<\/span><\/a><\/span><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 1:<\/strong>\u00a0<\/span>Enter the number <strong>1<\/strong> in an empty cell<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-19450\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-01.jpg\" alt=\"Turn Text To Values With Paste Special Values\" width=\"416\" height=\"344\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-01.jpg 416w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-01-300x248.jpg 300w\" sizes=\"(max-width: 416px) 100vw, 416px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 2:<\/strong><\/span> <strong>Copy<\/strong> that cell<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-19451\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-02.jpg\" alt=\"Turn Text To Values With Paste Special Values\" width=\"318\" height=\"464\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-02.jpg 318w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-02-206x300.jpg 206w\" sizes=\"(max-width: 318px) 100vw, 318px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 3:<\/strong><\/span> Select the data range, <strong>Right Click and select Paste Special<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-19453\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-03.jpg\" alt=\"Turn Text To Values With Paste Special Values\" width=\"440\" height=\"484\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-03.jpg 440w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/text-to-values-03-273x300.jpg 273w\" sizes=\"(max-width: 440px) 100vw, 440px\" \/><\/p>\n<p><span style=\"color: #ff0000;\"><strong>STEP 4:<\/strong><\/span> Select <strong>Values &amp; Multiply<\/strong> and press <strong>OK<\/strong><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/2015-11-26_23-14-30.png\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel Paste Special: A Values Multiplier\"  class=\"alignnone size-full wp-image-2061\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/2015-11-26_23-14-30.png\" sizes=\"(max-width: 398px) 100vw, 398px\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/2015-11-26_23-14-30-300x245.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/11\/2015-11-26_23-14-30.png 398w\" alt=\"Excel Paste Special: A Values Multiplier\" width=\"398\" height=\"325\" \/><\/a><\/p>\n<p>Your data will be transformed into values:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/correct-values.png\" rel=\"attachment wp-att-2440\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone size-full wp-image-2440\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/correct-values.png\" alt=\"Turn Text To Values With Paste Special Values\" width=\"164\" height=\"194\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"http:\/\/www.excel.tv\/41-john-michaloudis-excel-trainer-at-myexcelonline-com\/\" target=\"_blank\" rel=\"attachment noopener wp-att-2372 noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" title=\"Turn Text To Values With Paste Special Values\"  class=\"alignnone wp-image-2372 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/exceltv-e1455575259962.jpg\" alt=\"Turn Text To Values With Paste Special Values\" width=\"380\" height=\"102\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/exceltv-e1455575259962.jpg 380w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/02\/exceltv-e1455575259962-300x81.jpg 300w\" sizes=\"(max-width: 380px) 100vw, 380px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Many times you would have received data from your IT system which is formatted wrong! \u00a0Well a gazillion times if you work in a mid sized firm \ud83d\ude42 When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as\u00a0text rather than a value. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17637,"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":[6,41],"tags":[88,113],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2436"}],"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\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2436"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2436\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17637"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2436"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2436"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2436"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}