+ Reply to Thread
Results 1 to 5 of 5

Display the initial from a cluster of names

  1. #1
    Registered User
    Join Date
    07-13-2017
    Location
    Colombo, Sri Lanka
    MS-Off Ver
    2013
    Posts
    1

    Display the initial from a cluster of names

    Hi, How to get the initials in a full name where the name has several forenames. The excel column will consist of data that has varying number of forenames.

    Eg. A1 has the data: Shamalee Anne Perera A1 has the data Benjamin Russel Clifford Fernando

    Thanks, Priyanthi

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,240

    Re: Display the initial from a cluster of names

    Hi, welcome to the forum

    What would your expected answer look like?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,794

    Re: Display the initial from a cluster of names

    There are losts of User Defined Functions to return ALL of the letters:

    e.g. Firstletters, with this code pasted into a module.

    Please Login or Register  to view this content.
    I know no VBA, so I'd copyt one of these and use a simple formula to get the desired answer. However, I guarantee that it isn't as simple as that. Do you have names like Fred Bloggs Jnr ???

    =LEFT(FirstLetters(A1),LEN(FirstLetters(A1))-1)

    Enable macros on opening.
    Attached Files Attached Files
    Glenn



  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,634

    Re: Display the initial from a cluster of names

    or try
    =UPPER(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*0+1,256)),1)&
    LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*1+1,256)),1)&
    LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*2+1,256)),1)&
    LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*3+1,256)),1)&
    LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*4+1,256)),1)&
    LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",256)),256*5+1,256)),1))
    This will works up to 6 Words you can extend it as per your needs
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2211
    Posts
    36,794

    Re: Display the initial from a cluster of names

    I attached the wrong file!!!

    Of course, if you are wanting ALL the intials, just use

    =FirstLetters(A1)

    copied down.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Display output in initial cell only, not in repeating cells.
    By Mustang65 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2016, 03:46 PM
  2. Replies: 3
    Last Post: 03-05-2015, 02:33 PM
  3. [SOLVED] Create a Drop down that will list all names starting with initial letter of names.
    By blue leader in forum Excel - New Users/Basics
    Replies: 9
    Last Post: 01-16-2014, 03:58 PM
  4. [SOLVED] have list of names and want to switch names around by putting the first initial last
    By ctc75 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-10-2014, 01:09 PM
  5. Replies: 2
    Last Post: 06-28-2012, 01:35 PM
  6. Combo Box wont Display list on initial launch
    By oliver_l in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-22-2010, 01:53 AM
  7. Convert names into initial caps
    By Grd in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 02-16-2006, 02:50 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1