+ Reply to Thread
Results 1 to 3 of 3

Splitting characters based on conditions into different cells

  1. #1
    Forum Contributor
    Join Date
    09-19-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    140

    Splitting characters based on conditions into different cells

    Hey all,

    I have a list that looks like this:

    AVERNA,ROBERT C H/E
    Corn,Marlin Calvin LIV
    CATANESE,SALVATORE J & OLGA M
    JIMENEZ,ALEJANDRO & HILDA M
    Cortes,Alessandra M Marquez
    CATANESE,SALVATORE J & OLGA M & Nunez, Jason P

    The conditions:
    * If comma delineater between last and first name and there is no ampersand (&) in field, then delete everything after the middle initial/name, if the contents after the middle initial/name is three characters or less.
    * If comma delineater between last and first name and there is an ampersand (&) in field, concatenate the last name, which is always to the far left, to the first and middle initial (if one exists) located after the ampersand into the second column and remove everything after the ampersand, including the ampersand in the first column. In otherwords, we have two names in two different columns, both with same last name.
    * If comma delineater between last and first name and there is a string more than 3 characters long after the middle initial, then move the entire column exactly as it is into the third column. This indicates that the person has two last names.
    * If comma delineater between last and first name and there are two ampersands (&) in field, if the contents after one only has two strings where one to right is more than 3 characters, then move that two column 2 with the last name to far left and move the one after second ampersand to column 3 if same condiiton, but if that has a comma delineater, then don't concatenate it with name to far left, rather just keep it as is and move it to the third column. So this: CATANESE,SALVATORE J & OLGA M & Nunez, Jason P would look like this: CATANESE,SALVATORE J CATANESE, OLGA M Nunez, Jason P

    Is it possible to place all these conditions in a single line so that I just apply to entire list?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Splitting characters based on conditions into different cells

    this seems similar in nature to your other thread:

    http://www.excelforum.com/excel-gene...e-columns.html

    to which you did not even respond.

    Nor did you respond to those helping you on this thread:

    http://www.excelforum.com/excel-gene...om-string.html

    If you don't give feedback as to whether something has worked or not - people will lose interest in your threads as it may seem they're simply wasting their (freely donated) time.

  3. #3
    Forum Contributor
    Join Date
    09-19-2010
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2007
    Posts
    140

    Re: Splitting characters based on conditions into different cells

    I gave thanks now at those threads. I have been adding reputation points though. In fact, I went to give someone another and it said you have to spread around. I'm going to try to achieve this before I come back. At least I would come back with something.
    Last edited by johnmerlino; 10-21-2010 at 09:39 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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