+ Reply to Thread
Results 1 to 5 of 5

How to split names column to first and last name columns plus a notes column

  1. #1
    Registered User
    Join Date
    07-21-2017
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 2010
    Posts
    54

    How to split names column to first and last name columns plus a notes column

    Hi everyone, I have a column full of names but problem is some cells are blank or have more than one name plus text. Filling all the bank cells with a simple "N/A" was easy enough, however how can I split this up into first name, last name, and notes columns? Most cells have a name, followed by some text (e.g. John Smith (CEO)). Any suggestions are appreciated. Thank you!

    names.PNG

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: How to split names column to first and last name columns plus a notes column

    Please upload sample workbook with expected output. Without it, we will be guessing at what you may want.

    For an example. What should happen for John Doe, Jane Doe (MD)?

    Should it extract just John as First Name and Doe as Last Name and (MD) as note?
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Registered User
    Join Date
    07-21-2017
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 2010
    Posts
    54

    Re: How to split names column to first and last name columns plus a notes column

    Quote Originally Posted by CK76 View Post
    Should it extract just John as First Name and Doe as Last Name and (MD) as note?
    Actually yes, that would be perfect. Any little note like that I can manually edit but just splitting everything up would be fantastic

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,775

    Re: How to split names column to first and last name columns plus a notes column

    Not the most efficient formula I'm sure. But should do what you are looking for as per your sample.

    First Name:
    =IFERROR(LEFT(K2,FIND(" ",K2)-1),"N/A")

    Last Name:
    =IF(ISNUMBER(FIND(",",K2)),MID(K2,FIND(" ",K2)+1,FIND("@",SUBSTITUTE(K2,",","@"))-FIND(" ",K2)-1),IF(ISNUMBER(FIND("(",K2)),MID(K2,FIND(" ",K2)+1,LEN(K2)-FIND("(",K2)-1),TRIM(RIGHT(SUBSTITUTE(K2," ",REPT(" ",50)),50))))

    Contact Name Notes:
    =IFERROR(RIGHT(K2,LEN(K2)-FIND("@",SUBSTITUTE(K2,"(","@"))+1),"")

    See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-21-2017
    Location
    Toronto, Canada
    MS-Off Ver
    MS Excel 2010
    Posts
    54

    Re: How to split names column to first and last name columns plus a notes column

    Thank you!

+ 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] Split 3 data from one column to 3 different columns
    By naga in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 04-29-2017, 08:33 AM
  2. Need formula to split names/column
    By SM3T in forum Excel General
    Replies: 2
    Last Post: 04-20-2015, 07:06 AM
  3. [SOLVED] split comma separated names in a column to a new column
    By letsxcel in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-16-2012, 10:45 AM
  4. Split column of names into multiple columns
    By madaboutgolf in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-16-2011, 04:59 PM
  5. Split one column into five columns
    By maperalia in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-25-2006, 05:25 PM
  6. Split a column value into two columns
    By trjdba in forum Excel General
    Replies: 4
    Last Post: 01-23-2006, 03:50 PM
  7. split column into 4 columns
    By Jack in forum Excel General
    Replies: 3
    Last Post: 11-15-2005, 04:30 PM

Tags for this Thread

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