Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jul 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2000 - Extract names (Excel 2000)

    In Cell A1 I have Joe P. Doe
    In Cel A2 I have Anne P. Snow
    In Cell A3 I have Billy Holiday
    What I need. In Cell B1 first name only, C1 middle name if There is one, D1 last name.
    What I need. In Cell B2 first name only, C2 middle name, D2 last name.

    I think you get the idea. Extract data from one cell into multiply cells. HELP

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel 2000 - Extract names (Excel 2000)

    Chip Pearson's name formulas has the info that you need, but you will have to change them slightly for your format. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 2000 - Extract names (Excel 2000)

    If that is as complicated as it can get, it could be done with some formulas. However, could you have any of the following possibilities:

    Joe P Doe (No Period after the initial)
    Joe P Doe, Jr.
    Dr. Joe P. Doe, Sr. DDS
    Joe P. Von Doe
    etc.

    If your example did show the only possibilities, then these formula put is B1, C1, and D1 and copied down the columns should give you what you want:

    <pre>
    B1 =LEFT(A1,FIND(" ",A1)-1)
    C1 =IF(ISERROR(FIND(".",A1)),"",MID(A1,FIND(".",A1)-1,2))
    D1 =IF(ISERROR(FIND(".",A1)),RIGHT(A1,LEN(A1)-FIND(" ",A1)),RIGHT(A1,LEN(A1)-FIND(".",A1)-1))

    </pre>


    If it is any more complicated than your example, then it is probably going to take a macro.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •