People’s names come in many separate parts, some of which might not be present or known. The hassle begins when you’re dealing with a storage system?database or otherwise?where the parts are stored separately. You’re faced with the formidable task of putting it all together with correct formatting. A common mistake is formatting a person’s name whose middle initial is not known: John . Doe instead of John Doe. Using these functions, you can correct this problem without repeating effort:
Public Function FormatName(firstname As String, lastname As _ String, Optional mi As String, Optional title As String, _ Optional Suffix As String) As String Dim sRet As String If Len(Trim$(title)) > 0 Then sRet = StrConv(title, vbProperCase) If Right$(sRet, 1) <> "." Then sRet = sRet & "." sRet = sRet & " " End If If Len(Trim$(firstname)) > 0 Then sRet = sRet & StrConv(firstname, vbProperCase) & " " End If If Len(Trim$(mi)) > 0 Then sRet = sRet & StrConv(mi, vbProperCase) If Right$(sRet, 1) <> "." Then sRet = sRet & "." sRet = sRet & " " End If If Len(Trim$(lastname)) > 0 Then sRet = sRet & StrConv(lastname, vbProperCase) & " " End If If Len(Trim$(Suffix)) > 0 Then sRet = Trim$(sRet) & ", " & StrConv(Suffix, vbProperCase) End If FormatName = Trim$(sRet)End Function
The next function resembles the previous one, except that it puts the last name first:
Public Function FormatNameReverse(firstname As String, _ lastname As String, Optional mi As String) As String Dim sRet As String sRet = StrConv(lastname, vbProperCase) If Len(Trim$(firstname)) > 0 Or Len(Trim$(mi)) > 0 _ Then sRet = sRet & "," End If If Len(Trim$(firstname)) > 0 Then sRet = sRet & " " & Trim$(StrConv(firstname, _ vbProperCase)) End If If Len(Trim$(mi)) > 0 Then sRet = sRet & " " & Trim$(StrConv(Left$(mi, 1), _ vbProperCase)) & "." End If FormatNameReverse = Trim$(sRet)End Function