{"id":1184,"date":"2013-12-10T03:28:04","date_gmt":"2013-12-10T03:28:04","guid":{"rendered":"http:\/\/excelvbatutor.com\/?page_id=1184"},"modified":"2020-04-24T08:26:13","modified_gmt":"2020-04-24T08:26:13","slug":"excel-vba-2010-lesson-9-string-manipulation-functions","status":"publish","type":"page","link":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/","title":{"rendered":"Excel 2010 VBA Lesson 9: String Manipulation Functions"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-8-formatting-functions\/\">[Lesson 8]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-10-decision-making-1-if-then-else\/\">[Lesson 10]<\/a><\/strong><\/h4>\n\n\n\n<p>Excel 2010 VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as <strong>Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str, Chr<\/strong>, and <strong>Asc<\/strong> can be used in Excel 2010 VBA macro programming.<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\">9.1 The InStr function<\/h3>\n\n\n\n<p>InStr is a function that looks for and returns the position of a substring in a phrase.<\/p>\n\n\n\n<p><strong>Example 9.1<\/strong>\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub cmdInstr_Click()\nDim phrase As String\n phrase = Cells(1, 1).Value\n Cells(4, 1) = InStr(phrase, \u201cual\u201d)\nEnd Sub\n<\/pre>\n\n\n\n<p><br> The function InStr(phrase,\u201dual\u201d) will find the substring \u201cual\u201d from the phrase \u201cVisual Basic\u201d entered in cells(1,1) and then return its position, in this case, it is 4 from the left.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">9.2 The Left function<\/h3>\n\n\n\n<p>Left is a function that extracts the characters from a phrase, starting from the left.<\/p>\n\n\n\n<p>Left(phrase,4) means 4 characters are extracted from the phrase, starting from the leftmost position.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 9.2<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub cmdLeft_Click()\nDim phrase As String\n phrase = Cells(1, 1).Value\n Cells(2, 1) = Left(phrase, 4)\nEnd Sub\n<\/pre>\n\n\n\n<p>This code returns the substring \u201cVisu\u201d from the phrase \u201cVisual Basic\u201d entered in cells(1,1)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">9.3 The Right function<\/h3>\n\n\n\n<p>Right is a function that extracts the characters from a phrase, starting from the Right.<\/p>\n\n\n\n<p>Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 9.3<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub cmdRight_Click()\nDim phrase As String\n phrase = Cells(1, 1).Value\n Cells(3, 1) = Right(phrase, 5)\n<\/pre>\n\n\n\n<p>This code returns the substring \u201cBasic\u201d from the phrase \u201cVisual Basic\u201d entered in cells(1,1)<\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-3033628290023372\" data-ad-slot=\"9639157585\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<h3 class=\"wp-block-heading\">9.4 The Mid function<\/h3>\n\n\n\n<p>Mid is a function that extracts a substring from a phrase, starting from the position specified by the second parameter in the bracket.<\/p>\n\n\n\n<p>Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 9.4<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub cmdMid_Click()\nDim phrase As String\n phrase = Cells(1, 1).Value\n Cells(5, 1) = Mid(phrase, 8, 3)\nEnd Sub\n<\/pre>\n\n\n\n<p>This code returns the substring \u201cBas\u201d from the phrase \u201cVisual Basic\u201d entered in cells(1,1)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">9.5 The Len function<\/h3>\n\n\n\n<p>Len is a function that returns the length of a phrase(including empty space in between)<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Example 9.5<\/strong><\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">Private Sub cmdLen_Click()\nDim phrase As String\n phrase = Cells(1, 1).Value\n Cells(6, 1) = Len(phrase)\nEnd Sub\n<\/pre>\n\n\n\n<p>The code returns 12 for the phrase \u201cVisual Basic\u201d entered in cells(1,1)<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">9.6 The Ucase and the Lcase functions<\/h3>\n\n\n\n<p>The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters. For example,\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Ucase(\u201cexcel vba\u201d) =EXCEL VBA\n\nLcase(\u201cExcel VBA\u201d) =excel vba\n<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">9.7 The Str and Val functions<\/h3>\n\n\n\n<p>The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">9.8 The Chr and the Asc functions<\/h3>\n\n\n\n<p>The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for \u201cAmerican Standard Code for Information Interchange\u201d. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound. The format of the Chr function is\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Chr(charcode)\n<\/pre>\n\n\n\n<p>and the format of the Asc function is\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Asc(Character)\n<\/pre>\n\n\n\n<p>The following are some examples:\n<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc(\u201cB\u201d)=66, Asc(\u201c&amp;\u201d)=38\n<\/pre>\n\n\n\n<figure class=\"wp-block-embed\"><div class=\"wp-block-embed__wrapper\">\nhttp:\/\/excelvbatutor.com\/index.php\/page-sitemap.xml\n<\/div><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-8-formatting-functions\/\">[Lesson 8]<\/a>&lt;&lt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-tutorial\/\">[Table of Contents]<\/a>&gt;&gt;<a href=\"http:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-10-decision-making-1-if-then-else\/\">[Lesson 10]<\/a><\/strong><\/h4>\n","protected":false},"excerpt":{"rendered":"<p>[Lesson 8]&lt;&lt;[Table of Contents]&gt;&gt;[Lesson 10] Excel 2010 VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str, Chr, and Asc can be used in Excel 2010 VBA macro programming. 9.1 &hellip; <a href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\" class=\"more-link\">Continue reading<span class=\"screen-reader-text\"> &#8220;Excel 2010 VBA Lesson 9: String Manipulation Functions&#8221;<\/span><\/a><\/p>\n","protected":false},"author":5012,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"categories":[14],"tags":[],"class_list":["post-1184","page","type-page","status-publish","hentry","category-function"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.1.1 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor<\/title>\n<meta name=\"description\" content=\"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"og:description\" content=\"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming\" \/>\n<meta property=\"og:url\" content=\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\" \/>\n<meta property=\"article:modified_time\" content=\"2020-04-24T08:26:13+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\",\"url\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\",\"name\":\"Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"isPartOf\":{\"@id\":\"https:\/\/excelvbatutor.com\/#website\"},\"datePublished\":\"2013-12-10T03:28:04+00:00\",\"dateModified\":\"2020-04-24T08:26:13+00:00\",\"description\":\"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming\",\"breadcrumb\":{\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/excelvbatutor.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel 2010 VBA Lesson 9: String Manipulation Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/excelvbatutor.com\/#website\",\"url\":\"https:\/\/excelvbatutor.com\/\",\"name\":\"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor\",\"description\":\"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.\",\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/","og_locale":"en_US","og_type":"article","og_title":"Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","og_description":"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming","og_url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/","og_site_name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","article_modified_time":"2020-04-24T08:26:13+00:00","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/","url":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/","name":"Excel 2010 VBA Lesson 9: String Manipulation Functions - Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","isPartOf":{"@id":"https:\/\/excelvbatutor.com\/#website"},"datePublished":"2013-12-10T03:28:04+00:00","dateModified":"2020-04-24T08:26:13+00:00","description":"This lesson illustrates string manipulation functions in Excel 2010 VBA macro programming","breadcrumb":{"@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/excelvbatutor.com\/index.php\/excel-vba-2010-lesson-9-string-manipulation-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/excelvbatutor.com\/"},{"@type":"ListItem","position":2,"name":"Excel 2010 VBA Lesson 9: String Manipulation Functions"}]},{"@type":"WebSite","@id":"https:\/\/excelvbatutor.com\/#website","url":"https:\/\/excelvbatutor.com\/","name":"Learn Excel VBA Online \u2013 Step-by-Step Tutorials &amp; Courses | ExcelVBATutor","description":"Master Excel VBA with free tutorials, examples, and personalized guidance. Perfect for beginners and advanced users looking to automate Excel.","inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1184","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/users\/5012"}],"replies":[{"embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/comments?post=1184"}],"version-history":[{"count":33,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1184\/revisions"}],"predecessor-version":[{"id":3479,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/pages\/1184\/revisions\/3479"}],"wp:attachment":[{"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/media?parent=1184"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/categories?post=1184"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/excelvbatutor.com\/index.php\/wp-json\/wp\/v2\/tags?post=1184"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}