Results 1 to 9 of 9

Thread: Excel formula for retrieving Initials from Full Name

  1. #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.
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    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
    Attached Files Attached Files

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

  4. #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. #5
    Sorry..........i forgot to use the =UPPER()

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

  6. #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
    Attached Files Attached Files

  7. #7
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    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. #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. #9
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,481
    Articles
    0
    Excel Version
    365
    =proper(trim(b2))
    or just:
    =PROPER(B2)

Tags for this Thread

Posting Permissions

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