Yesterday I found myself needing to do some name splitting due to a changed input file layout from a vendor. Naturally, this was for one of the few systems that does not interface with our SQL Server at all, so I could not use functions I already had on there. Rather than writing convoluted queries, I decided to write a set of simple functions to help me in this task.
This is a common question, and hopefully my collection of functions can help someone with it. These are tailored to my situation, but I am sure could be altered to fit most types of 'FULLNAME' inputs that I have seen.
The functions are as follows:
fTrimPrefix - Removes prefix from name (also works for prefixes Mr. and Mrs., Dr. and Mrs.)
fTrimSuffix - Removes suffixes from name (I pass through this twice to handle for Dual Suffixes. Wanted to have it remove one at a time in order to use this function within the function to return suffixes)
fGrabFName - First passes your input through fTrimPrefix, then extracts and returns the first name.
fGrabMName - First passes your input through fTrimSuffix(twice) and fTrimPrefix, then extracts and returns middle name.
fGrabLName - First passes your input through fTrimSuffix(Twice), then extracts and returns last name.
fGrabPrefix - Extracts and returns prefix.
fGrabSuffix - Extracts and returns suffix. In some cases, passing through fTrimSuffix(once) and getting suffix1 from this value, and getting suffix2 from the original input.
These are kind of raw, and I am sure there are things that could be done to improve them (as well as suf/prefixes) that could be added to the list. I am always open to suggestions.
Hope they Help,
Note - I separated the code into blocks to make it more readable, but these are really meant to all work together. Some will work independently, some won't.
Public Function fTrimPrefix(InCol)
Dim OutCol As String
'replace " and " for entries containing "Mr. and Mrs." OutCol = Replace(Replace(InCol, " and ", " "), " & ", " ")
'check for nulls 'this is only necessary when selecting case Instr value - 1 If InStr(OutCol, " ") > 1 Then
'remove first prefix if present Select Case Left(OutCol, InStr(OutCol, " ") - 1) Case "Miss", "Mr.", "Ms.", "Mrs.", "Dr.", "Rev.", "Capt.", "Rabbi"
'I am running this twice --> fTrimSuffix(fTrimSuffix(FULLNAME)) 'when I need to trim the suffix. I only want it to trim one at a time 'so that it can be used with the fGrabSuffix function in returning 'dual suffixes
Dim OutCol As String
OutCol = InCol
'Remove Suffix if present Select Case Trim(Right(OutCol, InStr(StrReverse(OutCol), " ")))