+ Reply to Thread
Results 1 to 5 of 5

Splitting lastname, firstname,middleInitial,suffix into separate columnts

  1. #1
    Registered User
    Join Date
    05-17-2013
    Location
    Moon
    MS-Off Ver
    Excel 2013
    Posts
    20

    Question Splitting lastname, firstname,middleInitial,suffix into separate columnts

    Been trying to extract this column into separate columns. There is always ^ as a separator between the fields.

    Lastname^Firstname^^^II
    Last-name^First-name^^^III
    Lastname^Firstname^I^^MD
    Lastname^Firstname^Middlename
    Lastname^Firstname^Middlename^^II
    Lastname^Firstname

    Last name formula I'm using and this works fine for all entries, this one was easiest:
    =IFERROR(LEFT(B2,IF(ISERROR(FIND("^",B2,1)),LEN(B2),FIND("^",B2,1)-1)),"")

    First-name works on most except when "Lastname^Firstname" entry is encountered
    =IFERROR(MID(B2,SEARCH("^",B2,1)+1,SEARCH("^",B2,SEARCH("^",B2,1)+1)-SEARCH("^",B2,1)-1),"")

    Now for Middleinitial/Middlename and for Suffix I'm having trouble and would appreciate help

    Thanks in advance!

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Splitting lastname, firstname,middleInitial,suffix into separate columnts

    Have you tried using text-to-columns with ^ as the delimiter?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Splitting lastname, firstname,middleInitial,suffix into separate columnts

    A Function to Give you the desired part

    Please Login or Register  to view this content.
    ie
    Please Login or Register  to view this content.
    would return "John"

    While
    Please Login or Register  to view this content.
    would give a blank
    If someone has helped you then please add to their Reputation

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Splitting lastname, firstname,middleInitial,suffix into separate columnts

    I vote with Ron, this sort of setup is perfect for "Text to Columns"
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    05-17-2013
    Location
    Moon
    MS-Off Ver
    Excel 2013
    Posts
    20

    Re: Splitting lastname, firstname,middleInitial,suffix into separate columnts

    This worked like a charm! 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. Separate FirstName & FirstName from LastName
    By djw76 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-30-2014, 02:50 PM
  2. Separating lastname + suffix to separate columns
    By 2by4 in forum Excel General
    Replies: 6
    Last Post: 03-20-2014, 01:12 AM
  3. Replies: 6
    Last Post: 11-13-2012, 06:45 PM
  4. splitting name into firstname and lastname
    By arunjoshi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-21-2012, 04:13 AM
  5. Replies: 9
    Last Post: 04-18-2012, 06:27 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