Thread: Excel formula for retrieving Initials from Full Name

1. Excel formula for retrieving Initials from Full Name

Hi!

Am Xavier and my MS Office version is 2007

I need Full Names of my Clients to be displayed in certain manner, for the purpose of having Initials rather than Full Names.

Please refer the Excel file names "initials"

Thank you.

2. The formulae would be complex, so I offer a user defined function instead, see column M in the attached.
Formula used like:
=initials(B1,FALSE,FALSE)
where:
=initials(Source,IncludeMiddle,FiveSpaces)
Source is the source cell
IncludeMiddle is True/False
FiveSpaces is True/False

The udf in the file is:
Code:
```Function Initials(Source, IncludeMiddle As Boolean, FiveSpaces As Boolean)
xx = Split(Application.Trim(Source))
Fname = Left(xx(LBound(xx)), 1)
LName = Left(xx(UBound(xx)), 1)
MName = Left(xx(LBound(xx) + 1), 1)
Initials = Application.Trim(Join(Array(Fname, IIf(IncludeMiddle, MName, ""), LName)))
If FiveSpaces Then Initials = Fname & "     " & LName
End Function```

3. Thank you very much ..... it helped a lot.

4. Hi!

A small difference.....

I need everything in Column M to return CAPS...... even if the source cell contains 'small letters'

sorry for not mentioning it early.

5. Sorry..........i forgot to use the =UPPER()

Thank you .... i have achieved the results.......

6. Initials

Hello!

I have a small prob..regarding the last issue.

When i had 2 word names and 3 word names together (in a single column, all mixed up). where i tried using the function for 'names with 3 words' (in common) i.e. Xavier Joseph Kozhi = X J K ... the result for 3 words was perfect...
but names with two words gave a redundant result... i.e. Xavier Josep = X J J

Is it possible to have a function which handles both together?

Note: please refer to the part marked in Green in the File

7. try a change to the function (which includes making the result upper case):
Code:
```Function Initials(Source, IncludeMiddle As Boolean, FiveSpaces As Boolean)
xx = Split(Application.Trim(Source))
Fname = Left(xx(LBound(xx)), 1)
LName = Left(xx(UBound(xx)), 1)
MName = Left(xx(LBound(xx) + 1), 1)
Initials = UCase(Application.Trim(Join(Array(Fname, IIf(IncludeMiddle And UBound(xx) > 1, MName, ""), LName))))
If FiveSpaces Then Initials = Replace(Initials, " ", "     ")
End Function```

8. Hi!

I have a Excel column 'B' with MIDDLE NAMES, either it will be a word or a letter ( all i need is the 1st letter to be converted to CAPS, regardless its 1 word or letter)

E.g.:

ivana Ivana
m M
Geetha Geetha

Hope you got me!

9. =proper(trim(b2))
or just:
=PROPER(B2)