+ 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 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    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 2403
    Posts
    43,893

    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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    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 2403
    Posts
    43,893

    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. [SOLVED] 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