{"id":14883,"date":"2025-12-07T14:12:29","date_gmt":"2025-12-07T14:12:29","guid":{"rendered":"https:\/\/blog.bachi.net\/?p=14883"},"modified":"2025-12-07T14:40:09","modified_gmt":"2025-12-07T14:40:09","slug":"excel-string-to-date","status":"publish","type":"post","link":"https:\/\/blog.bachi.net\/?p=14883","title":{"rendered":"Excel String to Date"},"content":{"rendered":"<ul data-hveid=\"CEsQAQ\" data-ved=\"2ahUKEwjS553w0quRAxWG0QIHHatTCtsQm_YKegQISxAB\">\n<li><span data-huuid=\"15512133172400671376\"><code class=\"mv6bHd\">TEXT(value, format_text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400668749\">Converts numbers\/dates to text with specific formatting (e.g.,\u00a0<code class=\"mv6bHd\">TEXT(A1, \"0.00\")<\/code>\u00a0or\u00a0<code class=\"mv6bHd\">TEXT(B1, \"mm\/dd\/yy\")<\/code>).<span class=\"pjBG2e\" data-cid=\"c60e7197-9ff7-4016-93bf-0d904ee4db18\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"c60e7197-9ff7-4016-93bf-0d904ee4db18\" data-uuids=\"15512133172400671376,15512133172400668749\"><\/div>\n<\/li>\n<\/ul>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__75\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400671687\"><code class=\"mv6bHd\">LEFT(text, num_chars)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400669060\">Extracts characters from the left of a text string.<span class=\"pjBG2e\" data-cid=\"deeca724-8fed-4d97-aae0-c13560870cdd\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"deeca724-8fed-4d97-aae0-c13560870cdd\" data-uuids=\"15512133172400671687,15512133172400669060\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__82\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400671998\"><code class=\"mv6bHd\">RIGHT(text, num_chars)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400669371\">Extracts characters from the right of a text string.<span class=\"pjBG2e\" data-cid=\"3ac54eb7-9078-4774-9b8e-2e421f17978b\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"3ac54eb7-9078-4774-9b8e-2e421f17978b\" data-uuids=\"15512133172400671998,15512133172400669371\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__91\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400672309\"><code class=\"mv6bHd\">MID(text, start_num, num_chars)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400669682\">Extracts characters from the middle of a text string.<span class=\"pjBG2e\" data-cid=\"8c6176b4-bbc8-4b6b-afa2-b3478e6c3e29\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"8c6176b4-bbc8-4b6b-afa2-b3478e6c3e29\" data-uuids=\"15512133172400672309,15512133172400669682\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__100\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400672620\"><code class=\"mv6bHd\">TRIM(text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400669993\">Removes extra spaces from text (leading, trailing, and extra spaces between words).<span class=\"pjBG2e\" data-cid=\"f71ba616-4b16-4632-a996-f90c2e80aca6\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"f71ba616-4b16-4632-a996-f90c2e80aca6\" data-uuids=\"15512133172400672620,15512133172400669993\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__103\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400668835\"><code class=\"mv6bHd\">PROPER(text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400670304\">Capitalizes the first letter of each word.<span class=\"pjBG2e\" data-cid=\"a8ede29f-4781-4715-8fbc-304ff4307a24\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"a8ede29f-4781-4715-8fbc-304ff4307a24\" data-uuids=\"15512133172400668835,15512133172400670304\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__107\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400669146\"><code class=\"mv6bHd\">UPPER(text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400670615\">Converts text to all uppercase.<span class=\"pjBG2e\" data-cid=\"47e46473-ceac-4862-bc35-6f36e3010f6b\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"47e46473-ceac-4862-bc35-6f36e3010f6b\" data-uuids=\"15512133172400669146,15512133172400670615\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__111\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400669457\"><code class=\"mv6bHd\">LOWER(text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400670926\">Converts text to all lowercase.<span class=\"pjBG2e\" data-cid=\"2c537c06-a50b-4055-a6ad-0935bcd3ddd6\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"2c537c06-a50b-4055-a6ad-0935bcd3ddd6\" data-uuids=\"15512133172400669457,15512133172400670926\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__115\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400669768\"><code class=\"mv6bHd\">LEN(text)<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400671237\">Returns the number of characters in a text string.<span class=\"pjBG2e\" data-cid=\"422d0971-8d27-4f70-b9ff-ef961030457a\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span>\n<div class=\"NPrrbc\" data-cid=\"422d0971-8d27-4f70-b9ff-ef961030457a\" data-uuids=\"15512133172400669768,15512133172400671237\"><\/div>\n<\/li>\n<\/ul>\n<\/div>\n<div id=\"zok1adLIL4aji-gPq6ep2A0__118\" class=\"bsmXxe\" role=\"none\">\n<ul>\n<li><span data-huuid=\"15512133172400670079\"><code class=\"mv6bHd\">CONCAT<\/code>\u00a0\/\u00a0<code class=\"mv6bHd\">&amp;<\/code>:\u00a0<\/span><span data-huuid=\"15512133172400671548\">Combines text strings (e.g.,\u00a0<code class=\"mv6bHd\">=\"Hello \" &amp; A1 &amp; \"!\"<\/code>).<span class=\"pjBG2e\" data-cid=\"848d26f4-f8bb-4d31-833a-c762b485c1c8\"><span class=\"UV3uM\">\u00a0<\/span><\/span><\/span><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<\/div>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\n=TEXT()\r\n=VALUE()\r\n=MID()\r\n=LEFT()\r\n\r\n=DATE(\r\n  MID(\r\n    A1,\r\n    FIND(&quot;\/&quot;,A1)+1,\r\n    IND(\r\n      &quot;\/&quot;,\r\n      A1,\r\n      FIND(&quot;\/&quot;,A1)+1\r\n    )-FIND(&quot;\/&quot;,A1)-1\r\n  ),\r\n  LEFT(\r\n    A1,\r\n    FIND(&quot;\/&quot;,A1)-1\r\n  ),\r\n  MID(\r\n    A1,\r\n    FIND(&quot;\/&quot;,A1)+1,\r\n    FIND(&quot;\/&quot;,A1,FIND(&quot;\/&quot;,A1)+1)-FIND(&quot;\/&quot;,A1)-1)\r\n  )\r\n)\r\n<\/pre>\n<p><a href=\"https:\/\/support.microsoft.com\/en-us\/office\/find-function-c7912941-af2a-4bdf-a553-d0d89b0a0628\">FIND function<\/a><br \/>\n<a href=\"https:\/\/support.microsoft.com\/en-us\/office\/left-function-9203d2d2-7960-479b-84c6-1ea52b99640c\">LEFT function<\/a><br \/>\n<a href=\"https:\/\/support.microsoft.com\/en-us\/office\/mid-function-d5f9e25c-d7d6-472e-b568-4ecb12433028\">MID function<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>TEXT(value, format_text):\u00a0Converts numbers\/dates to text with specific formatting (e.g.,\u00a0TEXT(A1, &#8220;0.00&#8221;)\u00a0or\u00a0TEXT(B1, &#8220;mm\/dd\/yy&#8221;)).\u00a0 LEFT(text, num_chars):\u00a0Extracts characters from the left of a text string.\u00a0 RIGHT(text, num_chars):\u00a0Extracts characters from the right of a text string.\u00a0 MID(text, start_num, num_chars):\u00a0Extracts characters from the middle of a text string.\u00a0 TRIM(text):\u00a0Removes extra spaces from text (leading, trailing, and extra spaces between words).\u00a0 PROPER(text):\u00a0Capitalizes [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-14883","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/posts\/14883","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=14883"}],"version-history":[{"count":5,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/posts\/14883\/revisions"}],"predecessor-version":[{"id":14888,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=\/wp\/v2\/posts\/14883\/revisions\/14888"}],"wp:attachment":[{"href":"https:\/\/blog.bachi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=14883"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=14883"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bachi.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=14883"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}