Name to Initials (vba)

[Gerrit.B]

Is it possible to convert the name initials using vba?

Example: John Doe >> J.D.

G.

 

[Joe4]

If the format is always First Name followed by a single space followed by Last Name, then you can use a formula like this:

Initials: Left([Name],1) & "." & Mid([Name],InStr([Name]," ")+1,1) & "."

If you may have compound first names (like "Mary Ellen", "Bobby Joe"), then you may want to use the following slightly modifed version, which looks for the last space instead of the first:

Initials: Left([Name],1) & "." & Mid([Name],InStrRev([Name]," ")+1,1) & "."

 

[Gerrit.B]

Just found this working vba code to get the initials.

Option Compare Database
Option Explicit

Function Initials(name)
Dim chars() As String
Dim i, n As Integer

If IsEmpty(name) Then
Initials = ""
Else
n = Len(name)
ReDim chars(1 To n)
For i = 1 To n
chars(i) = Mid(name, i, 1)
Next i

Initials = Left(name, 1)
If n >= 3 Then
For i = 3 To n
If Mid(name, i - 1, 1) = " " Then
Initials = Initials & "." & Mid(name, i, 1)
End If
Next
End If
End If
End Function


Only the last missing . was missing so i added &"." to get it working.

G.

 

[Emjay P]

Here is a way to do it if you do not want the surname to be part of initials:

Public Function Initials(Names As Variant) As Variant
Dim chars(), FirstNames As String
Dim i, n As Integer
Dim arr
Dim Surname
arr = Split(Trim(Names), " ")
Surname = arr(UBound(arr))

FirstNames = Left(Names, Len(Trim(Names)) - Len(Surname) - 1)

If IsEmpty(Names) Then
Initials = ""
Else
n = Len(FirstNames)
ReDim chars(1 To n)
For i = 1 To n
chars(i) = Mid(FirstNames, i, 1)
Next i

Initials = Left(FirstNames, 1)
If n >= 3 Then
For i = 3 To n
If Mid(FirstNames, i - 1, 1) = " " Then
Initials = Initials &"."& Mid(FirstNames, i, 1)
End If
Next
End If
End If
End Function

 

[Micron]

Dim i, n As Integer
Dim chars(), FirstNames As String
What data type is i? chars()?

If you said Integer and string, incorrect. They are variants. Not sure if y'all realize that. If not, I'll say it's a common error when dimensioning multiple varibles on one line - which I often do, but this way:
Dim i as Integer, n as Integer
Dim chars() as String, FirstNames As String
Each has to be explicitly defined. Also, it is a good idea to declare the data type for an argument that is being passed:

Function Initials(name as String) <--unless coerced to some other type in the callling procedure, name is a variant also.
Name is a word that should not be used for a variable. See Microsoft Access tips: Problem names and reserved words in Access

Luckily, a variant will accept an empty string or null, but the code is only checking for "" If IsEmpty(Names) Then
What if a Null is passed? Null and empty string are not the same.
No doubt it all works as expected, but when it doesn't, those are a few reasons why it can give trouble.