CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
Names of people come in many separate parts, some of which can be not present or not known. The hassle begins if you are dealing with a storage system (database or otherwise) where the parts are stored separately. You are now faced with a formidable task of putting it all together and making sure that correct formatting is used.
A common mistake will format a person whose middle initial is not known as:
John . Doe instead of John Doe
However making it right requires some effort and there is no need to repeat it for every time. That’s why I created the following functions:
public Function FormatName(firstname as string, _
lastname as string, _
optional mi as string, _
optional title as string, _
optional Suffix as string) as string
If len(Trim$(title)) > 0 then
FormatName = StrConv(title, vbProperCase)
If Right$(FormatName, 1) <> "." then FormatName = _
FormatName & "."
FormatName = FormatName & " "
End If
If len(Trim$(firstname)) > 0 then
FormatName = FormatName & StrConv(firstname, _
vbProperCase) & " "
End If
If len(Trim$(mi)) > 0 then
FormatName = FormatName & StrConv(mi, vbProperCase)
If Right$(FormatName, 1) <> "." then FormatName = _
FormatName & "."
FormatName = FormatName & " "
End If
If len(Trim$(lastname)) > 0 then
FormatName = FormatName & StrConv(lastname, vbProperCase) _
& " "
End If
If len(Trim$(Suffix)) > 0 then
FormatName = Trim$(FormatName) & ", " & StrConv(Suffix, _
vbProperCase)
End If
FormatName = Trim$(FormatName)
End Function
This function is similar to the one above, except that it puts the lastname first.
public Function FormatNameReverse(firstname as string, _
lastname as string, _
optional mi as string) as string
FormatNameReverse = StrConv(lastname, vbProperCase)
If len(Trim$(firstname)) > 0 Or len(Trim$(mi)) > 0 then
FormatNameReverse = FormatNameReverse & ","
End If
If len(Trim$(firstname)) > 0 then
FormatNameReverse = FormatNameReverse & " " & _
Trim$(StrConv(firstname, vbProperCase))
End If
If len(Trim$(mi)) > 0 then
FormatNameReverse = FormatNameReverse & " " & _
Trim$(StrConv(Left$(mi, 1), _
vbProperCase)) & "."
End If
FormatNameReverse = Trim$(FormatNameReverse)
End Function
