HELP - EASY
HELP - EASY
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: HELP - EASY

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    hi.

    i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

    i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

    ex.

    William H. Dunham

    i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

    ex - (RIGHT(A2,SEARCH(" ",A2))

    returns- . Dunham

    because the search is reading left to right and not right to left.

    help!?

    thanks soooooooo much.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-04 13:38, moosemessier wrote:
    hi.

    i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

    i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

    ex.

    William H. Dunham

    i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

    ex - (RIGHT(A2,SEARCH(" ",A2))

    returns- . Dunham

    because the search is reading left to right and not right to left.

    help!?

    thanks soooooooo much.
    =RIGHT(A2,LEN(A2)-SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))&", "&LEFT(A2,SEARCH("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

    where A2 houses a name entry to be reversed.


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-04 13:38, moosemessier wrote:
    hi.

    i am new. i want to ask someone the functions used for string extraction. i am using excel2000 and have a question.

    i am manipulating data in a spreadsheet. i want to learn how to extract a last name at the end of a string and swap with the first.

    ex.

    William H. Dunham

    i used the left,right,search functions but cannot extract dunaham becuase of multiple blanks.

    ex - (RIGHT(A2,SEARCH(" ",A2))

    returns- . Dunham

    because the search is reading left to right and not right to left.

    help!?

    thanks soooooooo much.
    not sure if you want to keep the initial letter, if not use:

    =MID(MID(A1,FIND(" ",A1)+1,100),FIND(" ",MID(A1,FIND(" ",A1)+1,100))+1,100)&", "&LEFT(A1,FIND(" ",A1)-1)

    this also relies on there always being an intial, if this is not the case you'll have to use the above formula AND:

    =MID(A1,FIND(" ",A1)+1,100)&", "&LEFT(A1,FIND(" ",A1)-1)

    as two arguements of an if statement.

    Any help? I think Aladin's answer is nicer but I thought I'd have a stab.
    "Have a good time......all the time"
    Ian Mac

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    thanks so much guys.
    i will try the formula now!

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    that formula (1st one) works great.

    question- how do i use it for the following format?

    dunham, michael j.

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    bump

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    bump

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-05 07:36, moosemessier wrote:
    that formula (1st one) works great.

    question- how do i use it for the following format?

    dunham, michael j.
    The formula I proposed does exactly that.

    Aladin

  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no it does:

    j, dunham, mike

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    home
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    bumpitty bump bump

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com