+ Reply to Thread
Results 1 to 6 of 6

Parsing Names into Columns

  1. #1
    Registered User
    Join Date
    04-18-2009
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Parsing Names into Columns

    Hello guys,

    I recently got a job and I am going to be working with excel all the time. I was looking for some help on google and came across this site. I just got my first project and it looks easy but it's not that simple, esp for me. I would appreciate if i can get some help with this.

    This is what i have to do. I have this excel file and it has only one column and in that column there is a list of names, around 1000 of them. for example, the column looks like this:

    Names:

    John Smith
    Adam Zack
    Mark Kluzner
    Deep Patel

    etc

    My job is to divide that column into two different columns and have one column for first name and the second column for last name. I would like to know how would I get that done. I need serious help with this. I am looking forward to get some info in this.

    Thanks.

    The ending result should look like:

    Last Name - First Name
    John ---------- Smith
    Adam - ------- Zack
    Last edited by farman8; 04-18-2009 at 12:00 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Help with excel.

    First, welcome to the forum. I hate to be a stickler, but you need to go back to your first post, click on EDIT and change the title to something more descriptive of the issue, like "Parsing Names into Columns".

    Please read the forum rules, they actually help you and give you an idea of how you can search the forum to find your own answers...frequently because the TITLES are good ones!

    Next, your question. Highlight the entire data set in the single column.
    Click on DATA > TEXT TO COLUMNS > DELIMITED > NEXT > [x] Space / [x] Treat consecutive delimiters as one > Finish.

    You're probably 95% done at this point. Some people have weird 2/first names or 2/last names and you'll have to scan down and fix those. But it's a lot less work, eh? Should take...oh... 2 minutes start to finish.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983

    Re: Help with excel.

    farman8
    Welcome to Exceltip forum


    As pointed out by JBeaucaire
    Your thread title is in breach of our Forum Rules

    Please take a couple of minutes and read ALL the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1) so that it better discribes your problem/request
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help with excel.

    jb
    "parsing "
    now thats a good one, i only found out what that meant a year or so ago ,not exactly an everyday word, along with concatenation , an boolean. (maybe for users but not for newbies)
    may i suggest a simpler "split first and last names from one cell to two "

    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    04-18-2009
    Location
    NJ, USA
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Parsing Names into Columns

    sorry about that.. I've changed the title.


    JB: thank you so much. That was easy. But some of the ppl in the list have middle names or middle intials. there are around 450 of them. any way to fix that?
    Last edited by farman8; 04-18-2009 at 02:43 PM.

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Parsing Names into Columns

    Quote Originally Posted by farman8 View Post
    JB: thank you so much. That was easy. But some of the ppl in the list have middle names or middle intials. there are around 450 of them. any way to fix that?
    What do you mean "fix" it? The TEXT TO COLUMNS thing put all of those into cells of there own.

    Is what you really mean:
    Please Login or Register  to view this content.
    Ok, then you need some more blank columns. If names are in ColumnA starting at A1, enter these formulas:

    B1:
    =LEFT(A1,FIND(" ",A1)-1)

    C1:
    =IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)), FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+ 1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))

    D1:
    =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

    Copy them down the WHOLE 1000 rows of data.
    Then highlight the data
    Click Edit > Paste Special > Values (this removes the formulas)
    Delete column A.

    Done.
    (resource)
    Last edited by JBeaucaire; 04-18-2009 at 11:38 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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