Change the order of word in a excel cell

Thanks:  0
Likes:  0

# Thread: Change the order of word in a excel cell

1. ## 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. ## 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. ## 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))

4. ## Re: Change the order of word in a excel cell

Hi
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. ## 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. ## 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.

## User Tag List

#### Posting Permissions

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