Change the order of word in a excel cell

[dee3000]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
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].
<o> </o>
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
<o> </o>
Please also take note of the last name on the list, the end result when applying the foruma should read 'Van der Hoek, Kyile"

 

[Reid]

This should work. Hope it helps.

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

 

[Scott Huish]

Shouldn't need ISERROR.

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

 

[dee3000]

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

 

[Reid]

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.

 

[dee3000]

Thanks for forwarding your bank knowledge to me.

Thanks for both replies received on this post.