{"id":12137,"date":"2012-04-24T11:28:24","date_gmt":"2012-04-24T11:28:24","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/?p=1629"},"modified":"2012-04-24T11:28:24","modified_gmt":"2012-04-24T11:28:24","slug":"vba-programming-string-manipulation","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/","title":{"rendered":"VBA Programming : String Manipulation"},"content":{"rendered":"

Many a times, we come across to deal with Strings. Like Length of the String<\/strong>, Concatenating two or more Strings etc. This article teaches you how to deal with strings with the following different operations:
\n
\n1. Concatenating Strings (Joining Strings)
\n2. Finding Length of a String (Len Function)
\n3. Remove Spaces from a String (Trim Function)
\n4. Left Function
\n5. Right Function
\n6. MID Function
\n7. InStr Function
\n8. Chr Function
\n9. Asc Function
\n<\/strong><\/p>\n

<\/p>\n

Concatenating Strings (Join Strings) :<\/h2>\n

In Excel VBA, two strings can be Joined simply by adding them by using &<\/strong> sign. Refer the below example.<\/p>\n

\nPrivate Sub CommandButton1_Click()\nDim Strng1, Strng2, Strng3, RsltStrng As String\nStrng1 = \"My Name \"\nStrng2 = \"Is \"\nStrng3 = \"Vish\"\nRsltStrng = Strng1 & Strng2 & Strng3\nMsgBox (RsltStrng)\nEnd Sub\n<\/code><\/pre>\n
Result:<\/h3>\n
My Name Is Vish<\/strong>
\n<\/p>\n
Len Function<\/h2>\n
This Function is basically used to get the length of the String (Count of All letters including Spaces of a String).
\nIt returns an Integer Value.<\/p>\n
Syntax:<\/strong>
\n=Len(<\/strong>String<\/i>)<\/strong><\/p>\n
Example<\/strong><\/p>\n
=Len (Vishwa) = 6 and =Len (Learn Excel Macro) = 17<\/p>\n
<\/p>\n
Right  Function<\/h2>\n
The Right function extracts the right part of a String.
\nSyntax:<\/strong>
\nRight (, n)<\/p>\n
Where: <\/strong>
\nStr  : Is the Original String.
\nn    : Number of Character to be extracted from Right of the Original String
\n 
\nExample:<\/strong><\/p>\n
 Right(“Learn Excel Macro”, 5) = Macro
\n<\/p>\n
Left Function<\/h2>\n
The Left function extract the left part of a String. <\/p>\n
Syntax:<\/strong>
\nLeft (Str<\/i>, n)<\/p>\n
Where: <\/strong>
\nStr  : Is the Original String.
\nn    : Number of Character to be extracted from Left <\/strong>of the Original String
\n 
\nExample:<\/strong><\/p>\n
 Left(“Learn Excel Macro”, 5) = Learn<\/p>\n
<\/p>\n
Ltrim Function<\/h2>\n
The Ltrim function trims the empty spaces of the left portion of the String. <\/p>\n
Syntax:<\/strong>
\n=Ltrim(String<\/i>)<\/p>\n
Example:<\/strong><\/p>\n
 Ltrim (”   Learn Excel Macro  “)= Learn Excel Macro
\n<\/p>\n
Rtrim Function<\/h2>\n
The Rtrim function trims the empty spaces of the right part of the String.
\nSyntax:<\/strong>
\n=Rtrim(String<\/i>)<\/p>\n
 Example:<\/strong><\/p>\n
Rtrim (”  Learn Excel Macro    “) =     Learn Excel Macro
\n<\/p>\n
Trim function<\/h2>\n
Trim Function<\/strong> is combination of both RTrim and LTrim. It means, it removes all the spaces from Left and Right both the sides.
\nSyntax:<\/strong><\/p>\n
=Trim(String<\/i>)<\/p>\n
Example:<\/strong><\/p>\n
Trim (\u201c   Learn Excel Macro      \u201d) = Learn Excel Macro
\n<\/p>\n
Mid Function<\/h2>\n
 The Mid function extracts a sub-string from the original phrase or string. It takes the following format:
\nSyntax:<\/strong>
\nMid(string, position, n)
\nWhere:<\/strong><\/p>\n
String   : The Original String
\nPosition : Starting position from where extraction should start
\nn        : Number of Characters from the Start position<\/p>\n
 Example:<\/strong><\/p>\n
Mid(“Learn Excel Macro”, 3, 5) = rn Ex
\n<\/p>\n
InStr function<\/h2>\n
 The InStr function looks for a phrase or string that is embedded within the original phrase and returns the starting position of the embedded phrase. Basically this is a search function within a string.
\nSyntax:<\/strong><\/p>\n
Instr (n, String, Search)<\/p>\n
Where:<\/strong>
\nn        : Place from where Search Phrase will be started searching
\nString   : Original String where search string will be searched
\nSearch   : It is a search String which is searched in the Original String
\n 
\nExample:<\/strong>
\nInstr(1, “Learn Excel Macro, “Excel”)=7<\/p>\n
Note:<\/strong>It returns the position number where that Search String is found. In the above example, Excel word is found from 7th position of the Original String. That is why it returns 7.
\n<\/p>\n
Ucase and the Lcase functions<\/h2>\n
The Ucase function<\/strong> converts all the characters of a string to capital letters. On the other hand, the  Lcase function <\/strong> converts all the characters of a string to small letters. <\/p>\n
Example:<\/strong><\/p>\n
Ucase(\u201cLearn Excel Macro\u201d) =LEARN EXCEL MACRO<\/p>\n
Lcase(\u201cLearn Excel Macro\u201d) =learn excel macro
\n<\/p>\n
Str and Val functions<\/h2>\n
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.
\n<\/p>\n
Chr and the Asc functions<\/h2>\n
 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. There are 255 ASCII codes and as many ASCII characters.<\/p>\n
The format of the Chr function is<\/strong><\/p>\n
Chr(charcode)<\/p>\n
and the format of the Asc function is<\/strong><\/p>\n
Asc(Character)<\/p>\n
Example:<\/strong><\/p>\n
Chr(65)=A
\nChr(122)=z
\nAsc(“B”)=66<\/p>\n
<\/p>\n\n\n
\n 
\nTo Check out more Excel Macro Tutorials, visit Excel Macro Tutorial<\/a><\/strong>
\n\n<\/td>\n<\/tr>\n<\/table>\n<\/span>","protected":false},"excerpt":{"rendered":"
Many a times, we come across to deal with Strings. Like Length of the String, Concatenating two or more Strings etc. This article teaches you how to deal with strings with the following different operations: 1. Concatenating Strings (Joining Strings) 2. Finding Length of a String (Len Function) 3. Remove Spaces from a String (Trim […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1674,1675],"tags":[],"class_list":["post-12137","post","type-post","status-publish","format-standard","hentry","category-excel-macro-basics","category-excel-macro-for-beginners"],"yoast_head":"\nVBA Programming : String Manipulation - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"VBA Programming : String Manipulation\" \/>\n<meta property=\"og:description\" content=\"Many a times, we come across to deal with Strings. Like Length of the String, Concatenating two or more Strings etc. This article teaches you how to deal with strings with the following different operations: 1. Concatenating Strings (Joining Strings) 2. Finding Length of a String (Len Function) 3. Remove Spaces from a String (Trim […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2012-04-24T11:28:24+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"VBA Programming : String Manipulation\",\"datePublished\":\"2012-04-24T11:28:24+00:00\",\"dateModified\":\"2012-04-24T11:28:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\"},\"wordCount\":641,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro Basics\",\"Excel Macro Tutorial\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\",\"name\":\"VBA Programming : String Manipulation - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2012-04-24T11:28:24+00:00\",\"dateModified\":\"2012-04-24T11:28:24+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro Basics\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-macro-basics\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"VBA Programming : String Manipulation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"VBA Programming : String Manipulation - Let's excel in Excel","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:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/","og_locale":"en_US","og_type":"article","og_title":"VBA Programming : String Manipulation","og_description":"Many a times, we come across to deal with Strings. Like Length of the String, Concatenating two or more Strings etc. This article teaches you how to deal with strings with the following different operations: 1. Concatenating Strings (Joining Strings) 2. Finding Length of a String (Len Function) 3. Remove Spaces from a String (Trim […]","og_url":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2012-04-24T11:28:24+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/vmlogger.com_-1.png","type":"image\/png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"VBA Programming : String Manipulation","datePublished":"2012-04-24T11:28:24+00:00","dateModified":"2012-04-24T11:28:24+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/"},"wordCount":641,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro Basics","Excel Macro Tutorial"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/","url":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/","name":"VBA Programming : String Manipulation - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2012-04-24T11:28:24+00:00","dateModified":"2012-04-24T11:28:24+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2012\/04\/vba-programming-string-manipulation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro Basics","item":"https:\/\/vmlogger.com\/excel\/excel-macro-basics\/"},{"@type":"ListItem","position":3,"name":"VBA Programming : String Manipulation"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12137"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12137"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12137\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}