Get user initials from user name.

Posted by Ben on September 26, 2000 9:25 AM

Could someone help me with VBA code or a function to get a person's initials from a cell containing his full name? If Excel had a function the opposite of Concatenate, I could break the name into different cells and them take the leftmost letter from each cell, but unfortunately it doesn't. Is there a way?

-Ben

Posted by Tim Francis-Wright on September 26, 2000 11:03 AM

I tried to get a formula to do this,
but I got stuck trying to concatenate members
of an array into a single string--so I needed
a user-defined function.

Here's the function:
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

HTH

[Aside: My formula looked something like:
(IF(MID($B$15,ROW(INDIRECT("1:"&LEN($B$15)-1)),1)=" ",MID($B$15,ROW(INDIRECT("2:"&LEN($B$15))),1),""))
which isolates the initials other than
the first name, but has them locked in an array]

Posted by Ben on September 26, 2000 11:22 AM

Thanks, I'll try this! (n/t)

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