{"id":805,"date":"2020-05-09T19:51:35","date_gmt":"2020-05-09T19:51:35","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=805"},"modified":"2020-05-09T19:51:35","modified_gmt":"2020-05-09T19:51:35","slug":"how-to-have-vba-parse-a-name-string","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/","title":{"rendered":"How To Have VBA Parse A Name String"},"content":{"rendered":"<p>This post was actually an answer to someone&#8217;s question on one of my videos from a few years back.<\/p>\n<p><a href=\"https:\/\/www.youtube.com\/watch?v=UspqR0g4XyU\" rel=\"noopener noreferrer\" target=\"_blank\">https:\/\/www.youtube.com\/watch?v=UspqR0g4XyU<\/a><\/p>\n<p>The comment was:<\/p>\n<blockquote><p>How would you do this for the last name? Or second last name? How to find the space after the first space, and the space after the second space? And then, if there is no first space or there is a first space but no second space, then skip the function instead of error?<\/p><\/blockquote>\n<p>So the video was put out a few years back, and so I&#8217;m going to show you a newer way of handling this.<\/p>\n<p>Here is the worksheet and a little explanation:<br \/>\n<a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png?resize=459%2C453\" alt=\"\" width=\"459\" height=\"453\" class=\"aligncenter size-full wp-image-808\" \/><\/a><\/p>\n<p><strong>Basically, this is going to be one function, and one of my arguments is going to decide whether we need to parse the first name, last name, or middle name.<\/strong><\/p>\n<p>&#8230;And here are the answers to her questions:<\/p>\n<p><em><strong>How would you do this for the last name? <\/strong><\/em><\/p>\n<pre class=\"lang:vb decode:true \" >Function ParseName(ParseText As String, NamePart As String)\r\n    Dim strName As String\r\n        \r\n    Select Case NamePart\r\n          \r\n        Case \"Last\"\r\n            'gets the name after the last space\r\n            strName = Right(ParseText, Len(ParseText) - InStrRev(ParseText, \" \"))\r\n        \r\n    End Select\r\n    \r\n    ParseName = strName\r\n    \r\nEnd Function\r\n<\/pre>\n<p><em><strong>Or second last name? <\/strong><\/em><\/p>\n<p>Since that may mean &#8220;middle&#8221; name, I&#8217;m going to show that.<\/p>\n<p>The following will answer these 2 questions as well:<\/p>\n<p><em><strong>How to find the space after the first space, and the space after the second space? <\/p>\n<p>And then, if there is no first space or there is a first space but no second space, then skip the function instead of error?<\/strong><\/em><\/p>\n<pre class=\"lang:vb decode:true \" >Function ParseName(ParseText As String, NamePart As String)\r\n    Dim strName As String\r\n    \r\n    Dim varName As Variant\r\n    Dim intSpaceCount As Integer\r\n    \r\n    Dim intFirstSpace As Integer\r\n    Dim intLastSpace As Integer\r\n    \r\n    Select Case NamePart\r\n\r\n            \r\n        Case \"Middle\"\r\n            'parse the entire string by all the spaces in the string using the split function.\r\n            'then we will parse the check if there is a middle name based on the space count.\r\n\r\n            varName = Split(ParseText, \" \")\r\n            \r\n            For intSpaceCount = 0 To UBound(varName)\r\n                If intSpaceCount = 1 Then\r\n                    'no middle name\r\n                End If\r\n                If intSpaceCount = 2 Then\r\n                    'get middle name\r\n                    intFirstSpace = InStr(ParseText, \" \")\r\n                    intLastSpace = InStrRev(ParseText, \" \")\r\n                    \r\n                    strName = Mid(ParseText, intFirstSpace + 1, intLastSpace - intFirstSpace - 1)\r\n                End If\r\n            Next\r\n    End Select\r\n    \r\n    ParseName = strName\r\n    \r\nEnd Function\r\n<\/pre>\n<p>Here&#8217;s the code all together:<\/p>\n<pre class=\"lang:vb decode:true \" >Function ParseName(ParseText As String, NamePart As String)\r\n    Dim strName As String\r\n    \r\n    Dim varName As Variant\r\n    Dim intSpaceCount As Integer\r\n    \r\n    Dim intFirstSpace As Integer\r\n    Dim intLastSpace As Integer\r\n    \r\n    Select Case NamePart\r\n        \r\n        Case \"First\"\r\n            'gets the name before the first space\r\n            strName = Left(ParseText, InStr(ParseText, \" \"))\r\n            \r\n        Case \"Middle\"\r\n            varName = Split(ParseText, \" \")\r\n            \r\n            For intSpaceCount = 0 To UBound(varName)\r\n                If intSpaceCount = 1 Then\r\n                    'no middle name\r\n                End If\r\n                If intSpaceCount = 2 Then\r\n                    'get middle name\r\n                    intFirstSpace = InStr(ParseText, \" \")\r\n                    intLastSpace = InStrRev(ParseText, \" \")\r\n                    \r\n                    strName = Mid(ParseText, intFirstSpace + 1, intLastSpace - intFirstSpace - 1)\r\n                End If\r\n            Next\r\n    \r\n        Case \"Last\"\r\n            'gets the name after the last space\r\n            strName = Right(ParseText, Len(ParseText) - InStrRev(ParseText, \" \"))\r\n        \r\n    End Select\r\n    \r\n    ParseName = strName\r\n    \r\nEnd Function\r\n<\/pre>\n<p>Let me know if you have any questions, and make sure you share this with someone else.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post was actually an answer to someone&#8217;s question on one of my videos from a few years back. https:\/\/www.youtube.com\/watch?v=UspqR0g4XyU The comment was: How would you do this for the last name? Or second last name? How to find the space after the first space, and the space after the second space? And then, if [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-805","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How To Have VBA Parse A Name String - My Blog<\/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:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Have VBA Parse A Name String - My Blog\" \/>\n<meta property=\"og:description\" content=\"This post was actually an answer to someone&#8217;s question on one of my videos from a few years back. https:\/\/www.youtube.com\/watch?v=UspqR0g4XyU The comment was: How would you do this for the last name? Or second last name? How to find the space after the first space, and the space after the second space? And then, if [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/\" \/>\n<meta property=\"og:site_name\" content=\"My Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-05-09T19:51:35+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\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:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"headline\":\"How To Have VBA Parse A Name String\",\"datePublished\":\"2020-05-09T19:51:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/\"},\"wordCount\":261,\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/How-To-Have-VBA-Parse-A-Name-String.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/\",\"name\":\"How To Have VBA Parse A Name String - My Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/How-To-Have-VBA-Parse-A-Name-String.png\",\"datePublished\":\"2020-05-09T19:51:35+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#primaryimage\",\"url\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/How-To-Have-VBA-Parse-A-Name-String.png\",\"contentUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2020\\\/05\\\/How-To-Have-VBA-Parse-A-Name-String.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2020\\\/05\\\/09\\\/how-to-have-vba-parse-a-name-string\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Have VBA Parse A Name String\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\",\"name\":\"My Blog\",\"description\":\"My WordPress Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\\\/\\\/vbastring.com\\\/blog\"],\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Have VBA Parse A Name String - My Blog","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:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/","og_locale":"en_US","og_type":"article","og_title":"How To Have VBA Parse A Name String - My Blog","og_description":"This post was actually an answer to someone&#8217;s question on one of my videos from a few years back. https:\/\/www.youtube.com\/watch?v=UspqR0g4XyU The comment was: How would you do this for the last name? Or second last name? How to find the space after the first space, and the space after the second space? And then, if [&hellip;]","og_url":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/","og_site_name":"My Blog","article_published_time":"2020-05-09T19:51:35+00:00","og_image":[{"url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#article","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/"},"author":{"name":"admin","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"headline":"How To Have VBA Parse A Name String","datePublished":"2020-05-09T19:51:35+00:00","mainEntityOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/"},"wordCount":261,"image":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/","url":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/","name":"How To Have VBA Parse A Name String - My Blog","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#primaryimage"},"image":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png","datePublished":"2020-05-09T19:51:35+00:00","author":{"@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"breadcrumb":{"@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#primaryimage","url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png","contentUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2020\/05\/How-To-Have-VBA-Parse-A-Name-String.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vbastring.com\/blog\/2020\/05\/09\/how-to-have-vba-parse-a-name-string\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vbastring.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How To Have VBA Parse A Name String"}]},{"@type":"WebSite","@id":"https:\/\/vbastring.com\/blog\/#website","url":"https:\/\/vbastring.com\/blog\/","name":"My Blog","description":"My WordPress Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vbastring.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/vbastring.com\/blog"],"url":"https:\/\/vbastring.com\/blog\/author\/admin\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/805","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/comments?post=805"}],"version-history":[{"count":5,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/805\/revisions"}],"predecessor-version":[{"id":815,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/805\/revisions\/815"}],"wp:attachment":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/media?parent=805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/categories?post=805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/tags?post=805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}