{"id":459966,"date":"2022-06-08T18:00:37","date_gmt":"2022-06-08T15:00:37","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-extract-a-substring-in-microsoft-excel\/"},"modified":"2022-06-08T18:00:37","modified_gmt":"2022-06-08T15:00:37","slug":"how-to-extract-a-substring-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/","title":{"rendered":"#How to Extract a Substring in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6af76c2fc4421\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6af76c2fc4421\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/#%E2%80%9CHow_to_Extract_a_Substring_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Extract a Substring in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/#Which_Method_to_Use_for_Substring_Extraction\" >Which Method to Use for Substring Extraction?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/#Get_the_String_To_the_Left_of_Your_Text\" >Get the String To the Left of Your Text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/#Extract_the_String_to_the_Right_of_Your_Text\" >Extract the String to the Right of Your Text<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-extract-a-substring-in-microsoft-excel\/#Obtain_a_String_From_the_Middle_of_Your_Text\" >Obtain a String From the Middle of Your Text<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Extract_a_Substring_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Extract a Substring in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-731027\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel Logo.\" width=\"1200\" height=\"675\"\/><\/p>\n<p>If you want to extract a substring from the left, right, or middle of your text, you can use Microsoft Excel\u2019s <code>LEFT<\/code>, <code>RIGHT<\/code>, <code>MID<\/code>, <code>LEN<\/code>, and <code>FIND<\/code> functions to do that. We\u2019ll show you how.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Which_Method_to_Use_for_Substring_Extraction\"><\/span>Which Method to Use for Substring Extraction?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>What <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/765624\/basic-excel-functions-everybody-should-know\/\">method to use to extract a substring depends on where your substring is located.<\/p>\n<p>To extract a string from the left of your specified character, use the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#moka_anchor_left\">first method<\/a> below. To extract everything that\u2019s to the right of your specified character, use the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#moka_anchor_right\">second method<\/a> below. To extract a string from the middle of your text, use the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"#moka_anchor_mid\">third method<\/a> below.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>12 Basic Excel Functions Everybody Should Know<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Get_the_String_To_the_Left_of_Your_Text\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"moka_anchor_left\">Get the String To the Left of Your Text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you\u2019d like to get all the text that\u2019s to the left of the specified character in your cell, use Excel\u2019s <code>LEFT<\/code> and <code>FIND<\/code> functions to do that.<\/p>\n<p>First, open your spreadsheet and click the cell in which you want to see the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806033\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/1-left-select-cell.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Select a cell.\" width=\"650\" height=\"349\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In your selected cell, type the following function. In this function, replace <code>B2<\/code> with the cell where your full text is and <code>@<\/code> with the search character. The function will retrieve the entire string to the left of this character.<\/p>\n<p>Then press Enter.<\/p>\n<pre>=LEFT(B2,FIND(\"@\",B2)-1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806034\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/2-enter-left-function.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Use the LEFT function.\" width=\"649\" height=\"349\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Your selected cell will display the result of the function, which is the full text before your specified character in your cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806035\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/3-left-function-result.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"The result of the LEFT function.\" width=\"650\" height=\"315\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019re all set.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Extract_the_String_to_the_Right_of_Your_Text\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_2\"><a rel=\"nofollow noopener\" target=\"_blank\" name=\"moka_anchor_right\">Extract the String to the Right of Your Text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To get all the text that\u2019s to the right of the specified character in your cell, use Excel\u2019s <code>RIGHT<\/code> , <code>LEN<\/code> , and <code>FIND<\/code> functions.<\/p>\n<p>Start by launching your spreadsheet and clicking the cell in which you want to see the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806036\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/4-right-select-cell.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Choose a cell.\" width=\"650\" height=\"324\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the selected cell, enter the <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/791838\/excel-functions-data-entry\/\">following function. In this function, replace <code>B2<\/code> with the cell where your full text is and <code>@<\/code> with the search character. Excel will extract the entire string to the right of this character.<\/p>\n<p>Then press Enter.<\/p>\n<pre>=RIGHT(B2,LEN(B2)-FIND(\"@\",B2))<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806037\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/5-type-right-function.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Type the RIGHT function.\" width=\"650\" height=\"318\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll see the result of the function in your chosen cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806038\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/6-right-function-result.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"The result of the RIGHT function.\" width=\"650\" height=\"322\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019re done.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>13 Essential Excel Functions for Data Entry<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Obtain_a_String_From_the_Middle_of_Your_Text\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"moka_anchor_mid\">Obtain a String From the Middle of Your Text<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you\u2019d like to extract a string containing a specific number of characters located at a certain position in your cell, use Excel\u2019s <code>MID<\/code> function.<\/p>\n<p>In your spreadsheet, select the cell where you want to display the resulting string.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806040\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/7-mid-select-cell.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"\" width=\"645\" height=\"322\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the selected cell, enter the following function. In this function, replace <code>B2<\/code> with the cell where you have the full text, <code>1<\/code> with the position of the character where you want to start the string selection, and <code>3<\/code> with the number of characters you want to extract.<\/p>\n<p>Then press Enter.<\/p>\n<pre>=MID(B2,1,3)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806041\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/8-enter-mid-function.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Enter the MID function.\" width=\"650\" height=\"312\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Excel will extract the specified number of characters from the given position in your cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-806042\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/05\/9-mid-function-result.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"The result of the MID function.\" width=\"650\" height=\"344\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And that\u2019s all there is to it.<\/p>\n<hr\/>\n<p>Like this, you can also <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/769974\/how-to-count-cells-with-text-in-microsoft-excel\/\">count the number of cells with text in your Excel spreadsheets. Check out our guide to learn how.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Cells With Text in Microsoft Excel<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/806027\/excel-substring\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Extract a Substring in Microsoft Excel&#8221; If you want to extract a substring from the left, right, or middle of your text, you can use Microsoft Excel\u2019s LEFT, RIGHT, MID, LEN, and FIND functions to do that. We\u2019ll show you how. Which Method to Use for Substring Extraction? What method to use to&#8230;<\/p>\n","protected":false},"author":1,"featured_media":459967,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/05\/MS-excel-logo-675.png?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-459966","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/459966","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=459966"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/459966\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/459967"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=459966"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=459966"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=459966"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}