Tag: vba

String mangling in Excel/VBA

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)