Reverse names and deleting common occurence.

Hi all,

I have a task of copying lots of names to a worksheet, and the problem is they are coming accross in reverse order. eg. Smith John instead of John Smith. I have found a formula which does this, however some names have (a) on the end of them eg. Smith John (a) and sometimes without a space eg Smith John(a).

What I need is for the names to be reversed and have the (a) deleted if it occurs.

Can anyone help?

Thanks,

Yappa

Hello Yappa,

with data in A1 try

=TRIM(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,255),"(a)",""))&" "&LEFT(A1,FIND(" ",A1)-1)

daddylonglegs that's perfect! Thanks mate.

Is there anyway I could get the (a) if it occurs to be printed in the next column over, or am I looking at vba for that?

Yappa

Try

=IF(COUNTIF(A1,"*(a)"),"(a)","")