{"id":1170,"date":"2015-04-24T19:11:40","date_gmt":"2015-04-24T17:11:40","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=1170"},"modified":"2024-04-05T23:11:46","modified_gmt":"2024-04-05T21:11:46","slug":"excels-text-function","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excels-text-function\/","title":{"rendered":"Excel\u00b4s TEXT Function"},"content":{"rendered":"<p>&nbsp;<\/p>\n<p><em><strong>What does it do? <\/strong><\/em><\/p>\n<p>Converts a numeric value to text and lets you specify the display formatting by using special format strings<\/p>\n<p><em><strong>Formula breakdown: <\/strong><\/em><\/p>\n<p>=TEXT(<span style=\"color: #0000ff\">value<\/span>,<span style=\"color: #ff6600\"> format text<\/span>)<\/p>\n<p><em><strong>What it means: <\/strong><\/em><\/p>\n<p>=TEXT(<span style=\"color: #0000ff\">a numeric value or a formula<\/span>, <span style=\"color: #ff6600\">a text string enclosed in quotation marks<\/span>)<\/p>\n<hr \/>\n<p>The <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/extract-text-with-substring\/\" target=\"_blank\" rel=\"noopener\">TEXT function in Excel<\/a> allows you to convert a numeric value to a specific format by using special format strings.<\/p>\n<p>If you have a date and want to show just the month or if you have a large number and want to show it in a thousands format , then the <strong>TEXT<\/strong> function is your savior.<\/p>\n<p>An example usage of the <strong>TEXT<\/strong> would be:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" title=\"Excel\u00b4s TEXT Function\" class=\"aligncenter size-full wp-image-9847\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text1.png\" alt=\"Excel\u00b4s TEXT Function\" width=\"772\" height=\"130\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text1.png 772w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text1-300x51.png 300w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text1-768x129.png 768w\" sizes=\"(max-width: 772px) 100vw, 772px\" \/><\/p>\n<p>Following is a demonstration and a table of the different ways you can display a value in Excel by using the <strong>TEXT<\/strong> function:<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/ev91cMOdJ-E?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><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><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2015\/04\/Text.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\">Download excel workbook<i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Text.xlsx<\/span><\/a><\/p>\n<\/div>\n<p class=\"line-two\"><span style=\"color: #0000ff\"><strong>How to Use the Text Formula in Excel<\/strong><\/span><\/p>\n<p>&nbsp;<\/p>\n<table width=\"556\">\n<tbody>\n<tr>\n<td width=\"173\"><strong>To display<\/strong><\/td>\n<td width=\"210\"><strong>As<\/strong><\/td>\n<td width=\"173\"><strong>Use this format<\/strong><\/td>\n<\/tr>\n<tr>\n<td>1234.59<\/td>\n<td>1234.6<\/td>\n<td>&#8220;####.#&#8221;<\/td>\n<\/tr>\n<tr>\n<td>8.9<\/td>\n<td>8.900<\/td>\n<td>&#8220;#.000&#8221;<\/td>\n<\/tr>\n<tr>\n<td>0.631<\/td>\n<td>0.6<\/td>\n<td>&#8220;0.#&#8221;<\/td>\n<\/tr>\n<tr>\n<td>1234.568<\/td>\n<td>1234.6<\/td>\n<td>&#8220;#.0#&#8221;<\/td>\n<\/tr>\n<tr>\n<td>5.25<\/td>\n<td width=\"210\">5 1\/4<\/td>\n<td>&#8220;# ???\/???&#8221;<\/td>\n<\/tr>\n<tr>\n<td>12000<\/td>\n<td>12000<\/td>\n<td>&#8220;#,###&#8221;<\/td>\n<\/tr>\n<tr>\n<td>12000<\/td>\n<td>12<\/td>\n<td>&#8220;#,&#8221;<\/td>\n<\/tr>\n<tr>\n<td>12200000<\/td>\n<td>12.2<\/td>\n<td>&#8220;0.0,,&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Months<\/td>\n<td>1\u201312<\/td>\n<td>&#8220;m&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Months<\/td>\n<td>01\u201312<\/td>\n<td>&#8220;mm&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Months<\/td>\n<td>Jan\u2013Dec<\/td>\n<td>&#8220;mmm&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Months<\/td>\n<td>January\u2013December<\/td>\n<td>&#8220;mmmm&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Months<\/td>\n<td>J\u2013D<\/td>\n<td>&#8220;mmmmm&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Days<\/td>\n<td>1\u201331<\/td>\n<td>&#8220;d&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Days<\/td>\n<td>01\u201331<\/td>\n<td>&#8220;dd&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Days<\/td>\n<td>Sun\u2013Sat<\/td>\n<td>&#8220;ddd&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Days<\/td>\n<td>Sunday\u2013Saturday<\/td>\n<td>&#8220;dddd&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Years<\/td>\n<td>00\u201399<\/td>\n<td>&#8220;yy&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Years<\/td>\n<td>1900\u20139999<\/td>\n<td>&#8220;yyyy&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Hours<\/td>\n<td>0\u201323<\/td>\n<td>&#8220;h&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Hours<\/td>\n<td>00\u201323<\/td>\n<td>&#8220;hh&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Minutes<\/td>\n<td>0\u201359<\/td>\n<td>&#8220;m&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Minutes<\/td>\n<td>00\u201359<\/td>\n<td>&#8220;mm&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Seconds<\/td>\n<td>0\u201359<\/td>\n<td>&#8220;s&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Seconds<\/td>\n<td>00\u201359<\/td>\n<td>&#8220;ss&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Time<\/td>\n<td>4:00 AM<\/td>\n<td>&#8220;h AM\/PM&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Time<\/td>\n<td>4:36 PM<\/td>\n<td>&#8220;h:mm AM\/PM&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Time<\/td>\n<td>4:36:03 PM<\/td>\n<td>&#8220;h:mm:ss A\/P&#8221;<\/td>\n<\/tr>\n<tr>\n<td>Time<\/td>\n<td>36:03.8<\/td>\n<td>&#8220;h:mm:ss.00&#8221;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&nbsp; What does it do? Converts a numeric value to text and lets you specify the display formatting by using special format strings Formula breakdown: =TEXT(value, format text) What it means: =TEXT(a numeric value or a formula, a text string enclosed in quotation marks) The TEXT function in Excel allows you to convert a numeric [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17456,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Excel\u00b4s TEXT Function","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[3,14],"tags":[43,70],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1170"}],"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=1170"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/1170\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17456"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=1170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=1170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=1170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}