+ Reply to Thread
Results 1 to 16 of 16

Splitting Names in Excel

  1. #1
    Registered User
    Join Date
    07-26-2013
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Splitting Names in Excel

    Hi All,

    I am having some issues trying to split names in Excel. I am aware of 'text to columns' but it doesn't quiet do what I am after. My data is show below -

    GRILLO Emiliano
    KRUGER Jbe
    OTTO Hennie
    LEE Craig
    OLAZÁBAL José María
    SINGH Jeev Milkha
    VAN DER WALT Tjaart

    I need to find a way to split the names by capital letters so they would read as below. That way I can use VLOOKUP in the way I want.

    GRILLO Emiliano
    KRUGER Jbe
    OTTO Hennie
    LEE Craig
    OLAZÁBAL José María
    SINGH Jeev Milkha
    VAN DER WALT Tjaart

    Whenever I try to split it I can't find a way to do it without using a space or comma etc. The space between Van and Der is where it goes wrong. Unfortunately I can't find a way to do this (Excel Novice!), so any ideas would be greatly appreciated.

    Thanks all!

    Ryan

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Splitting Names in Excel

    What's the difference between the first list and the second list?

    Pete

  3. #3
    Registered User
    Join Date
    07-26-2013
    Location
    London,England
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Splitting Names in Excel

    Sorry Pete, the first set of names are in one cell. The second set of names should be split to have capital letters (The surname) in one column and the christian name in the second column.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Splitting Names in Excel

    In B1 enter the following array formula ( commit with Ctrl+Shift+Enter)
    Please Login or Register  to view this content.
    Then in C1
    Please Login or Register  to view this content.
    ( Supposing first name is always in upper case)

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    @Pepe Le Mokko

    Unfortunately this fails for OLAZÁBAL José María as Á has CODE(Á)=193.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    Try this array formula for the first names:

    =TRIM(MID(A1,MATCH(1,(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>=97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1)))-2,1))=32),0)-1,255))

    Then use Pepe Le Mokko's for the surnames.

    Regards

    EDIT: Just realised that this will fail also in such a case as VAN DER LÁ Tjaart or some such - these diacritics are a problem!
    Last edited by XOR LX; 07-26-2013 at 05:22 AM.

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Splitting Names in Excel

    I wouldn't even know how to type this kind of letters on the keyboard

  8. #8
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    Me neither!

    And there doesn't appear to be a (at least non-VBA) method of reducing accented letters to non-accented letters, so will have to play around with these CODE()s a bit more, though looks problematic.

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    Maybe:

    =TRIM(MID(A7,MATCH(1,(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7))),1))>=97)*(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7)))-2,1))=32)*(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7)))+1,1))<>32),0)-1,255))

    But perhaps (probably) exceptions can be found here as well.

  10. #10
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Splitting Names in Excel

    Seems to work with
    Please Login or Register  to view this content.
    (only covers the Á case though...)

  11. #11
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    Sure, but that only accounts for Á. What about all the other accented characters?

    I think that my last solution might actually be ok - unless anyone can find an exception?

    Regards
    Last edited by XOR LX; 07-26-2013 at 05:56 AM.

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Splitting Names in Excel

    Try this CSE array in C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    And in B2
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  13. #13
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    @Marcol

    And what happens in your formula in cases where the second letter of the first name is accented, such as:

    OLAZÁBALÁ Jésé Méría?

    Regards

  14. #14
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Splitting Names in Excel

    Try this after checking VBScript Regular Expressions 5.5 at Tools/References.
    Other special characters should be entered next to "Á" in the square brackets.
    Please Login or Register  to view this content.

  15. #15
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Splitting Names in Excel

    "Maybe:

    =TRIM(MID(A7,MATCH(1,(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7))),1))>=97)*(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7)))-2,1))=32)*(CODE(MID(A7,ROW(INDIRECT("1:"&LEN(A7)))+1,1))<>32),0)-1,255))

    But perhaps (probably) exceptions can be found here as well.
    "

    Has anyone found a counter-example to disprove this yet?

    Regards

  16. #16
    Registered User
    Join Date
    05-10-2013
    Location
    Pomáz, Hungary
    MS-Off Ver
    Excel 2007
    Posts
    78

    Re: Splitting Names in Excel

    Try this to handle special characters:
    Enter the array formula in B1 (with Ctrl+Shift+Enter):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Enter in C1 (with Enter):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this works for you too.

+ 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. [SOLVED] Splitting names
    By rwl518p in forum Excel General
    Replies: 4
    Last Post: 09-04-2012, 05:23 PM
  2. splitting names
    By jweaver in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2010, 04:05 PM
  3. Splitting first and last names
    By DLeland in forum Excel General
    Replies: 3
    Last Post: 12-28-2008, 03:22 PM
  4. New to VBA and Excel, need help splitting a name field and dumping extra middle names
    By Jonnymorris in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-05-2008, 08:44 AM
  5. splitting out names
    By craigster in forum Excel General
    Replies: 2
    Last Post: 04-25-2008, 05:43 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