Excel formula for retrieving Initials from Full Name

K

kjxavier1975

New member
Joined
Apr 2, 2014
Messages
8
Reaction score
0
Points
0
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.
 

Attachments

  • Initials.xlsx
    10.2 KB · Views: 27
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
 

Attachments

  • Initials.xlsm
    15.7 KB · Views: 22
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.
 
Sorry..........i forgot to use the =UPPER()

Thank you .... i have achieved the results.......
 
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
 

Attachments

  • sec- Initials.xlsm.xlsm
    15.8 KB · Views: 21
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
 
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!
 
=proper(trim(b2))
or just:
=PROPER(B2)
 
You must log in or register to reply here.
Back
Top