Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Thanked 0 Times in 0 Posts

    Reversomg Names (Excel 2000)

    I have a large list of names some of them are first name (space) Last name. The others are Last Name, First name. Is there any way to reverse the incorrect ones so all the names are in one format. I need it in Last Name, First name.

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

    Re: Reversomg Names (Excel 2000)

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

    Yes you can do that using a variety of formulas, or VBA. But lets go with formulas first:

    1) Mid, this one can extract a string from another.
    2) Find, this will find a character in a string, so you will need it to find the space.
    3) Right and Left, are formulas that can extract the Right or Left side of a string up to a specified number of characters.
    4) the CONCATENATE, which is a formula that can combine text stings together

    So now you can do:

    1) Find the Space, what is to its left will be the Last Name what is to its Right will be the First Name.
    2) Use the Right and Left to split them into two cells.
    3) Combine the strings into a cell,

    And you are done. So forget about the VBA solution, but if you still want it, post a reply.

    Hope this helps.

    <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
    Gold Lounger
    Join Date
    Feb 2001
    Dublin, Ireland, Republic of
    Thanked 0 Times in 0 Posts

    Re: Reversomg Names (Excel 2000)

    Assuming all the entries with surname first include a comma (,) and the remainder (the ones you want to change) do not, the following formula should work for an entry in A1,

    =IF(NOT(ISERROR(FIND(",",A1))),A1,RIGHT(A1,LEN(A1)-FIND(" ",A1))&", "&LEFT(A1,FIND(" ",A1)-1))

    Andrew C

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Reversomg Names (Excel 2000)

    I think the best way is just to use data text to columns delimited space to separate the names into 2 columns.
    Then create a formul in a new column (assume you had them in A and now they are in col A and B after separating) in col C enter the formula:

    Copy this down the rows till the data end. This will be TRUE for last, first names and FALSE for those first last
    sort the list by col c. with data sort
    Delete column C.
    Select the range of Lastnames from col A (ones that end in comma) and move the range to col C
    Select all the cells in B and C that have blanks in A and move B&C to col A&B
    Highlight the list and find replace "," for null
    Now you have a list fo first names in A and last in B
    I would keep them separate. If you want to display them in a format I would use another column and use something like:
    <pre>=B1&", "&A1</pre>

    to combine them. It is so much easier to combine the items with "&" than it is to separate them


Posting Permissions

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