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", "")
If InStr(Len(newInstitutionName) - 1, newInstitutionName, "U") Then
newInstitutionName = Left(newInstitutionName, Len(newInstitutionName) - 2) & " University"
FormatInstitutionName = newInstitutionName
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:
Search and Replace in Multiple Files
perl -pi -w -e 's/search/replace/g;' *.php
Source: How to do a search and replace over multiple files
Compare two strings (when one of them is a subprocess)
diff <(md5 -q ~/Downloads/ubuntu-16.04.1-desktop-amd64.iso) <(echo "17643c29e3c4609818f26becf76d29a3")
Source: Process Substitution