{"id":344,"date":"2020-10-13T13:59:37","date_gmt":"2020-10-13T08:29:37","guid":{"rendered":"http:\/\/exceltrick.com\/?p=344"},"modified":"2023-08-11T23:27:37","modified_gmt":"2023-08-11T17:57:37","slug":"vba-substring-function","status":"publish","type":"post","link":"https:\/\/exceltrick.com\/vba\/vba-substring-function\/","title":{"rendered":"VBA Substring &#8211; How to Substring in Excel Macro"},"content":{"rendered":"<p>Substring is one of the most popular functions in any programming language. It eases your tasks while dealing with strings. As the name suggests a substring function divides a string into different parts based on particular criteria.<\/p>\n<p>There are multiple <i>VBA Substring functions<\/i>. In practical situations, these substring functions can be quite useful in extracting a portion of a string.<\/p>\n<p>Today in this post I am going to explain all the VBA substring functions that you can use in Excel macros:<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Substring_Function.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-348\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/Substring_Function.png\" alt=\"Substring Function in Excel VBA\" width=\"434\" height=\"308\" title=\"\"><\/a>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_53 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\" role=\"button\"><label for=\"item-69f392f491ddb\" ><span class=\"\"><span style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" 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: #999;color:#999\" 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 aria-label=\"Toggle\" aria-label=\"item-69f392f491ddb\"  type=\"checkbox\" id=\"item-69f392f491ddb\"><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#LEFT_Substring_function\" title=\"LEFT Substring function:\">LEFT Substring function:<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#Examples\" title=\"Examples:\">Examples:<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#Right_Substring_function\" title=\"Right Substring function:\">Right Substring function:<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#Example\" title=\"Example:\">Example:<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#MID_Substring_function\" title=\"MID Substring function:\">MID Substring function:<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#Example-2\" title=\"Example:\">Example:<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#SPLIT_Substring_function\" title=\"SPLIT Substring function:\">SPLIT Substring function:<\/a><ul class='ez-toc-list-level-3'><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#Example-3\" title=\"Example:\">Example:<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/exceltrick.com\/vba\/vba-substring-function\/#VBA_Substring_Macro_Example\" title=\"VBA Substring Macro Example:\">VBA Substring Macro Example:<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"LEFT_Substring_function\"><\/span>LEFT Substring function:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The <a href=\"https:\/\/exceltrick.com\/functions\/excel-left-function\/\">LEFT function<\/a> in Excel VBA is used for fetching a specified number of characters from the start of the string. The syntax of the LEFT function is as follows:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Left (text_string, length)<\/pre>\n<\/div>\n<ul>\n<li>Here \u2018text_string\u2019 refers to an input string that is to be separated.<\/li>\n<li>And \u2018length\u2019 refers to the number of characters to be extracted.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Examples\"><\/span>Examples:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Left (<span style=\"color: #ba2121;\">\"Exceltrick\"<\/span>, <span style=\"color: #666666;\">5<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives an output \"Excel\"<\/span><br clear=\"none\" \/>Left (<span style=\"color: #ba2121;\">\"SomeText\"<\/span>, <span style=\"color: #666666;\">4<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives the result \"Some\"<\/span><\/pre>\n<\/div>\n<p><b>Note: <\/b>Instead of using a hardcoded string in the first argument you can also fetch \u2018text_string\u2019 from your excel sheet like ActiveSheet.Range(&quot;A1&quot;).<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Right_Substring_function\"><\/span>Right Substring function:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The RIGHT Function in Excel VBA is just opposite to the LEFT function. It returns a specified number of characters from the end of the text string. The syntax of the RIGHT function is as follows:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Right (text_string, length)<\/pre>\n<\/div>\n<ul>\n<li>Here \u2018text_string\u2019 refers to an input string that is to be separated.<\/li>\n<li>And \u2018length\u2019 refers to the number of characters to be extracted but extraction begins from the right side.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Example\"><\/span>Example:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Right (<span style=\"color: #ba2121;\">\"Exceltrick\"<\/span>, <span style=\"color: #666666;\">5<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives an output \"trick\"<\/span><br clear=\"none\" \/>Right (<span style=\"color: #ba2121;\">\"SomeText\"<\/span>, <span style=\"color: #666666;\">4<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives the result \"Text\"<\/span><\/pre>\n<\/div>\n<p><b>Note: <\/b>Instead of using a hardcoded string in the first argument you can also fetch \u2018text_string\u2019 from your excel sheet as ActiveSheet.Range(&quot;A1&quot;).<\/p>\n<h2><span class=\"ez-toc-section\" id=\"MID_Substring_function\"><\/span>MID Substring function:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MID is a much better function than the first two, it gives you the ability to specify the start and end positions of the extracted string. The syntax of the <a title=\"Excel MID Function \u2013 How to Use\" href=\"http:\/\/exceltrick.com\/functions\/excel-mid-function\/\">MID VBA Substring function<\/a> is as under:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Mid(text_string, start_position, Length)<\/pre>\n<\/div>\n<ul>\n<li>Here \u2018text_string\u2019 refers to an input string that is to be separated.<\/li>\n<li>\u2018start_position\u2019 refers to the numeric position from where extraction is to be started.<\/li>\n<li>And \u2018length\u2019 refers to the number of characters to be extracted.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Example-2\"><\/span>Example:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">MID (<span style=\"color: #ba2121;\">\"Exceltrick\"<\/span>, <span style=\"color: #666666;\">2<\/span>,<span style=\"color: #666666;\">4<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives an output \"celt\"<\/span><br clear=\"none\" \/>MID (<span style=\"color: #ba2121;\">\"SomeText\"<\/span>, <span style=\"color: #666666;\">4<\/span>,<span style=\"color: #666666;\">4<\/span>) <span style=\"color: #408080; font-style: italic;\">'gives the result \"Text\"<\/span><\/pre>\n<\/div>\n<p><b>Note: <\/b>Instead of using a hardcoded string in the first argument you can also fetch \u2018text_string\u2019 from your excel sheet as ActiveSheet.Range(&quot;A1&quot;).<\/p>\n<h2><span class=\"ez-toc-section\" id=\"SPLIT_Substring_function\"><\/span>SPLIT Substring function:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The SPLIT function is another VBA function that can be used for sub-stringing or splitting a string. The SPLIT function can come very handy when you are dividing a text string into more than one parts based on a delimiter. The syntax of a split function is as under:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Split (text_string, Delimiter, limit, Compare)<\/pre>\n<\/div>\n<ul>\n<li>Here, \u2018text_string\u2019 refers to an input string that is to be separated.<\/li>\n<li>\u2018Delimiter\u2019 refers to the delimiter character which separates the string into parts. This is an optional argument, if it is left blank then, the space character &quot; &quot; is assumed to be the default delimiter.<\/li>\n<li>\u2018limit\u2019 refers to the maximum number of substring parts into which the string should be divided. It is also an optional argument, the default value is (-1) which means that substring should happen at every position where the delimiter is encountered.<\/li>\n<li>\u2018compare\u2019 is an optional numerical value that specifies the comparison to use when evaluating substrings.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Example-3\"><\/span>Example:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For instance, you have a text string as &quot;This is a text string&quot; and now you have to break this string into individual words, so in this case, you will use space &quot; &quot; as a delimiter. The split function will be used as:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\">Split (<span style=\"color: #ba2121;\">\"This is a text string\"<\/span>, <span style=\"color: #ba2121;\">\" \"<\/span>)<\/pre>\n<\/div>\n<p>The <b>result of this split function<\/b> is an <b>array<\/b> of words: &quot;This&quot; &quot;is&quot; &quot;a&quot; &quot;text&quot; &quot;string&quot;.<\/p>\n<p><em> Recommended Reading:<\/em> <a title=\"VBA Split Function \u2013 How to Use\" href=\"http:\/\/exceltrick.com\/vba\/vba-split-function\/\"  rel=\"noopener noreferrer\">VBA Split Function<\/a><\/p>\n<h2><span class=\"ez-toc-section\" id=\"VBA_Substring_Macro_Example\"><\/span>VBA Substring Macro Example:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Below I have created a macro that illustrates all the substring techniques available in VBA programming. This is a simple and self-explanatory macro, in this, I have simply divided a text string with the 4 methods that I have described above.<\/p>\n<a href=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA-Substring-Macro.png\"><img decoding=\"async\" class=\"aligncenter size-full wp-image-349\" src=\"http:\/\/exceltrick.com\/wp-content\/uploads\/2013\/01\/VBA-Substring-Macro.png\" alt=\"VBA Substring Macro\" width=\"740\" height=\"313\" title=\"\"><\/a>\n<p>Below is the code that I have used for this macro:<\/p>\n<div class=\"vbaFormula\">\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008000; font-weight: bold;\">Sub<\/span> <span style=\"color: #0000ff;\">BreakStrings<\/span>()<br clear=\"none\" \/><span style=\"color: #408080; font-style: italic;\">'Left function<\/span><br clear=\"none\" \/>a <span style=\"color: #666666;\">=<\/span> Left(<span style=\"color: #ba2121;\">\"Excel Trick Text\"<\/span>, <span style=\"color: #666666;\">5<\/span>)<br clear=\"none\" \/><span style=\"color: #408080; font-style: italic;\">'Right function<\/span><br clear=\"none\" \/>b <span style=\"color: #666666;\">=<\/span> Right(<span style=\"color: #ba2121;\">\"Excel Trick Text\"<\/span>, <span style=\"color: #666666;\">11<\/span>)<br clear=\"none\" \/><span style=\"color: #408080; font-style: italic;\">'Mid function<\/span><br clear=\"none\" \/>c <span style=\"color: #666666;\">=<\/span> Mid(<span style=\"color: #ba2121;\">\"Excel Trick Text\"<\/span>, <span style=\"color: #666666;\">1<\/span>, <span style=\"color: #666666;\">11<\/span>)<br clear=\"none\" \/><span style=\"color: #408080; font-style: italic;\">'Split function<\/span><br clear=\"none\" \/>d <span style=\"color: #666666;\">=<\/span> Split(<span style=\"color: #ba2121;\">\"Excel Trick Text\"<\/span>, <span style=\"color: #ba2121;\">\" \"<\/span>)<br clear=\"none\" \/><span style=\"color: #008000; font-weight: bold;\">For<\/span> <span style=\"color: #008000; font-weight: bold;\">Each<\/span> wrd <span style=\"color: #aa22ff; font-weight: bold;\">In<\/span> d<br clear=\"none\" \/>strg <span style=\"color: #666666;\">=<\/span> strg <span style=\"color: #666666;\">&amp;<\/span> wrd <span style=\"color: #666666;\">&amp;<\/span> <span style=\"color: #ba2121;\">\", \"<\/span><br clear=\"none\" \/><span style=\"color: #008000; font-weight: bold;\">Next<\/span><br clear=\"none\" \/><span style=\"color: #408080; font-style: italic;\">'Displaying the results in a mesage box<\/span><br clear=\"none\" \/>MsgBox <span style=\"color: #ba2121;\">\"Left: \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> a <span style=\"color: #666666;\">&amp;<\/span> vbNewLine <span style=\"color: #666666;\">&amp;<\/span> <span style=\"color: #ba2121;\">\"Right: \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> b <span style=\"color: #666666;\">&amp;<\/span> vbNewLine <span style=\"color: #666666;\">&amp;<\/span> <span style=\"color: #ba2121;\">\"Mid: \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> c <span style=\"color: #666666;\">&amp;<\/span> vbNewLine <span style=\"color: #666666;\">&amp;<\/span> <span style=\"color: #ba2121;\">\"Split: \"<\/span> <span style=\"color: #666666;\">&amp;<\/span> strg<br clear=\"none\" \/><span style=\"color: #008000; font-weight: bold;\">End<\/span> <span style=\"color: #008000; font-weight: bold;\">Sub<\/span><\/pre>\n<\/div>\n<p>So, this was all about VBA substring functions. Do share your view related to the topic.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Substring is one of the most popular functions in any programming language. It eases your tasks while dealing with strings. As the name suggests a substring function divides a string into different parts based on particular criteria. There are multiple VBA Substring functions. In practical situations, these substring functions can be quite useful in extracting [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[9],"_links":{"self":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/344"}],"collection":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/comments?post=344"}],"version-history":[{"count":0,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/posts\/344\/revisions"}],"wp:attachment":[{"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/media?parent=344"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/categories?post=344"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltrick.com\/wp-json\/wp\/v2\/tags?post=344"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}