Inverting comma delimited names

[Krispy]

Problem:
I have a list of 1000+ names. Some are in conventional format eg: "Joe Bloggs" while others are inverted / comma delimited eg: "Bloggs, Joe".

Previously, I have got round this by converting text to columns (cell A to B & C) and then combining eg C1&" "&B1

Would it be possible to automate this using a macro that doesn't split the column? I was thinking something along the lines ...

For all cells in range
If cell.value contains ", " then
RHS & " " & LHS
End if
Next

Obviously the above is nonsence code but that is my line of thinking

 

[Mike Blackman]

Hi,

Maybe something like this;

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

 

[Weaver]

This formula might do the job

=IF(ISERROR(FIND(",",A1)),A1,TRIM(MID(A1,FIND(",",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(",",A1)-1)))

 

[Richard Schollar]

Hi

You could use this formula and copy down:

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

 

[schielrn]

Here is a VBA approach that is very good:

http://www.mrexcel.com/archive/VBA/30934.html

Hope that helps.

 

[Krispy]

Here is a VBA approach that is very good:

http://www.mrexcel.com/archive/VBA/30934.html

Hope that helps.

Perfect