Results 1 to 6 of 6

Change the order of word in a excel cell

This is a discussion on Change the order of word in a excel cell within the Excel Questions forums, part of the Question Forums category; Hi I have a list of 200 client names in the format [Surname], {Firstname] as listed below, however some of ...

  1. #1
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Change the order of word in a excel cell


    Hi
    I have a list of 200 client names in the format [Surname], {Firstname] as listed below, however some of the names are [Firstname] [Lastname]. These both names are grouped together in the 1 cell (Note:I have highlighted with * and they dont have a comma).
    I require a formula to go through the list, identify the cell with No comma, and to swap text around such that it reads [Surname], [Firstname].

    Algie, Leanne
    *Andrew Monsif
    Eather, Ryan
    Kulevski, Ken
    *Damon Gillmore
    Syeda, Sanjana
    Baricz, Andrea
    *Andrew Praulins
    Nixon, Annette
    *Janet Kazmierczak
    Sciacca, Judi
    *Kylie Van der Hoek

    Please also take note of the last name on the list, the end result when applying the foruma should read 'Van der Hoek, Kyile"

  2. #2
    Board Regular
    Join Date
    Oct 2004
    Posts
    593

    Default Re: Change the order of word in a excel cell

    This should work. Hope it helps.

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

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    16,973

    Default Re: Change the order of word in a excel cell

    Shouldn't need ISERROR.

    =IF(ISNUMBER(FIND(",",A1)),A1,MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1))
    Office 2007/2010

  4. #4
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Change the order of word in a excel cell

    Hi
    Both worked perfectly...thats the answers...
    Just for learnings, could you maybe walk me through what each step of the formula is doing?
    As i dislike copy pasting work...
    Thanks

  5. #5
    Board Regular
    Join Date
    Oct 2004
    Posts
    593

    Default Re: Change the order of word in a excel cell

    Well we can break down the Ferrari version provided by Hotpepper:

    =IF(ISNUMBER(FIND(",",A1)),A1,MID(A1&", "&A1,FIND(" ",A1)+1,LEN(A1)+1))

    FIND(",",A1) returns the position of the first comma in cell A1 or an error if there is no comma. This is a simple test to see if the names need to be reversed. If there is a comma, IF(ISNUMBER(FIND(",",A1))) will be true and the value in A1 will be used.

    If it is not true, Hotpepper uses the MID string function to construct the correct string. MID returns a substring of another string based on a starting point and a desired length. For example, =MID("abcdef",3,3) results in "cde" since it returns a three character substring that starts with the third character of the base string.

    In this case, the base string is A1&", "&A1. If the value in A1 was "Andrew Monsif", this would result in a base string of "Andrew Monsif, Andrew Monsif".

    The MID function let's you pull the "Monsif, Andrew" out of the middle of this if you can specify the right starting point and length. You find the starting point by finding the first space using FIND(" ",A1), which returns 7 in this case. You can find the length of the original string easily enough using LEN(A1). Since we need to account for the comma that has been added, the length we need is LEN(A1)+1.

    LEFT and RIGHT are similar to MID. Since I didn't come up with the A1&", "&A1 trick, those are the functions that I used. Not nearly as elegant, but they get you to the same place. HTH.

  6. #6
    New Member
    Join Date
    Jan 2012
    Posts
    4

    Default Re: Change the order of word in a excel cell

    Thanks for forwarding your bank knowledge to me.

    Thanks for both replies received on this post.

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