Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seperating Names (2002)

    I know the Lounge can help here AGAIN!!
    I have some old spreadsheets (that were put together by someone with even less knowledge than me!), with a large list of names all in one column - i.e. Smith Bill. I have two options 1) put the Bill into A1 and the Smith into B1 or 2) turn them around to Bill Smith in A1. Any ideas Guru's

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Seperating Names (2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Dean

    OK basically you should check the Text functions to deal with this issue.

    For example:

    =LEFT(A1,FIND(" ",A1,1)-1) will get you what is to the Left of the Space <font color=red>Smith</font color=red>

    =RIGHT(A1,FIND(" ",A1,1)-2) will get you what is to the Right of the Space <font color=red> Bill</font color=red>

    Now how these two functions work:

    Left needs 2 arguments From what string, A1 has the names so it will use that, and how many characters. Well here comes the Find, which takes three arguments:
    Find what, a space, in what string, A1 and starting from what character, First = 1. That will yield a number for the Left function and then we deduct the space and that is where the -1 comes in

    Right does the same, except from the right, and the -2 is because there is the Space and your next character.

    test these, and also look at the MID functions.

    <font color=red> Warning:</font color=red> if there are multiple spaces in the name, such as Mc Adam, or what have you in the French language you will need to use more complex functions.

    Hope this helps.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating Names (2002)

    Thanks very much Wassim - will give it a shot!!

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating Names (2002)

    Another thing to check is the Data, text-to-columns menufunction.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating Names (2002)

    Wassam,

    The left function works fine however the right is returning variable results ie Atkin Nicholas is returning 'olas Atkin'

    Any thoughts?

    <img src=/S/aussie.gif border=0 alt=aussie width=21 height=22> Dean

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperating Names (2002)

    Try

    =MID(A2,FIND(" ",A2,1)+1,9999)

    (the 9999 is just a number longer than your cell values are going to be; any other large number would do)

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Seperating Names (2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Dean

    OK try this one for size:

    =RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

    This finds the length of whatever is in A1, so eg if you have Bob it will give you 3. Mind you it also counts spaces.

    Find will give you the position of the space, so we will subtract that from the length, and get to the character just after the space.

    Now I know that other members of the Lounge gave you excellent advice, I wanted to give you some info maybe you can use it in future projects.

    Wassim is my name <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Seperating Names (2002)

    I think you have what you need, but for future reference, http://www.cpearson.com/excel/FirstLast.htm.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating Names (2002)

    And lastly (really?) and a bit off-topic, if you think that mailing labels etc generated from lists in the form JOE BLOGGS are ugly and look like they've been done by machine(!), the =PROPER function will convert them to Joe Bloggs and I leave you to work out how to handle the resulting Fred Mctavish and his Glaswegian chums. (And then tell me, please!)

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Seperating Names (2002)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    I was actually hoping that soon I will have enough time to write a VBA function to handle all these special cases. You do have Arabic, French, German, and Irish/Scotish names that fall into these special cases, and they are all known.

    Would you like to be involved in writing this function?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Seperating Names (2002)

    The best way to handle this is to NEVER put the names in upper case in a database. <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Put them in properly. If you need to display/print/compare as all uppercase, then use the function (UCASE in VB) to change "on the fly".

    Steve

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seperating Names (2002)

    <!profile=MarkD>MarkD<!/profile> posted a function that handles some exceptions in <post#=309766>post 309766</post#>

  13. #13
    Lounger
    Join Date
    Oct 2003
    Location
    Birmingham, England
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seperating Names (2002)

    Wassim,
    I'm flattered to be asked but a) concerning Arabic, my computer only writes forwards ie left to right and [img]/forums/images/smilies/cool.gif[/img] concerning vba, my knowledge is such that the world's smallest postage stamp is too vast a canvas on which to describe the sum total of All I Know.
    My workaround is to ensure that anything I put into a list in the McDonald, MacDonald, d'Artagnan, d'Arcy etc departments is in the correct case (for me) in the first place.
    How would you propose to handle amibidextrous names like Macdonald or FitzWarren / Fitzwarren? And the poet WALTER DE LA MERE?
    Interesting thread!
    Best of luck with your project, anyway.

Posting Permissions

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