# Thread: Excel formula for retrieving Initials from Full Name

1. ## Excel formula for retrieving Initials from Full Name

Register for a FREE account, and/
or Log in to avoid these ads!

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"

i appreciate your early reply.

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!

I appreciate your early reply....

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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•