Often, I find myself needing to do string mangling in spreadsheets. For instance, in an Excel dump of an MS Access database, the names of institutions were sometimes written as “Foo, University of” or “Foo U”. I wanted these to be formatted as “University of Foo” and “Foo University” respectively. I may be misinformed but there doesn’t seem to be regexp style substitution in VBA. I could do this directly in the spreadsheet, but conditional use of functions gets to be hard to understand and difficult to edit. So, I wrote the following custom function in VBA.<\/p>\n
Function FormatInstitutionName(institutionName As Variant) As String<\/span>\r\n'Formats an institution name for<\/span> import<\/span> into CiviCRM:\r\n'\"Foo, University of\"<\/span> => \"University of Foo\"<\/span>\r\n'\"Foo U\"<\/span> => \"Foo University\"<\/span>\r\n Dim newInstitutionName As String<\/span>\r\n\r\n newInstitutionName = institutionName\r\n\r\n If InStr(newInstitutionName, \", University of\"<\/span>) Then\r\n newInstitutionName = \"University of \"<\/span> & Replace(newInstitutionName, \", University of\"<\/span>, \"\")\r\n End If\r\n\r\n If InStr(Len(newInstitutionName) - 1, newInstitutionName, \"U\"<\/span>) Then\r\n newInstitutionName = Left(newInstitutionName, Len(newInstitutionName) - 2) & \" University\"<\/span>\r\n End If\r\n\r\n FormatInstitutionName = newInstitutionName\r\nEnd Function<\/pre>\n<\/div>\n<\/div>\nAdd this to your workbook by following the instructions at http:\/\/www.vertex42.com\/ExcelArticles\/user-defined-functions.html<\/a><\/p>\nThen call it in your spreadsheet cells like like:<\/p>\n
\n\n=FormatInstitutionName(C2)<\/pre>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"Often, I find myself needing to do string mangling in spreadsheets. For instance, in an Excel dump of an MS Access database, the names of institutions were sometimes written as “Foo, University of” or “Foo U”. I wanted these to be formatted as “University of Foo” and “Foo University” respectively. I may be misinformed but… Continue reading String mangling in Excel\/VBA<\/span><\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[27,4],"tags":[774,16546,775,61,469,777,776],"class_list":["post-1826","post","type-post","status-publish","format-standard","hentry","category-hacks","category-note-to-self","tag-excel","tag-microsoft","tag-office","tag-programming","tag-replace","tag-string","tag-vba","entry"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4wnIz-ts","_links":{"self":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/1826","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/comments?post=1826"}],"version-history":[{"count":1,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/1826\/revisions"}],"predecessor-version":[{"id":1827,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/posts\/1826\/revisions\/1827"}],"wp:attachment":[{"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/media?parent=1826"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/categories?post=1826"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blogs.terrorware.com\/geoff\/wp-json\/wp\/v2\/tags?post=1826"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}