String mangling in Excel/VBA
December 7th, 2009 | Published in hacks, note to self
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.
Function FormatInstitutionName(institutionName As Variant) As String 'Formats an institution name for import into CiviCRM: '"Foo, University of" => "University of Foo" '"Foo U" => "Foo University" Dim newInstitutionName As String newInstitutionName = institutionName If InStr(newInstitutionName, ", University of") Then newInstitutionName = "University of " & Replace(newInstitutionName, ", University of", "") End If If InStr(Len(newInstitutionName) - 1, newInstitutionName, "U") Then newInstitutionName = Left(newInstitutionName, Len(newInstitutionName) - 2) & " University" End If FormatInstitutionName = newInstitutionName End Function
Add this to your workbook by following the instructions at http://www.vertex42.com/ExcelArticles/user-defined-functions.html
Then call it in your spreadsheet cells like like:
=FormatInstitutionName(C2)
Leave a Response