Within a cell, put data before comma after the comma and vice versa

[snsd]

In our company's SharePoint Address Book, our display names are:

Smith, John (ABC Inc.)

When I view in Excel, I simply want to see John Smith.

I was able to get rid of the (ABC Inc.) buy using the substitute function:

i.e. =SUBSTITUTE(A1," (ABC Inc.)","")

I do not know how to flip the "Smith, John" to get "John Smith". I'm hoping this can be done without VBA - but if VBA is necessary, that's OK.

Thanks in advance for any assistance you might be able to offer.

Dave

 

[VoG]

With display name in A1 try

=MID(A1,FIND(",",A1)+2,FIND("(",A1)-FIND(",",A1)-2)&LEFT(A1,FIND(",",A1)-1)

 

[snsd]

Brilliant! Thanks so much Peter. Works perfectly.

Dave

 

[ExcelDane]

edited: Peter/VoG came up with a better solution than mine

 

[Ruddles]

=TRIM(MID(A1,FIND(",",A1)+1,80))&" "&TRIM(LEFT(A1,FIND(", ",A1)-1))

where Smith, John is in A1.