Need help showing department names in ()

[jenny1013]

I have a report that shows people names First Name Lastname (Dept) I have a different system that needs people as Lastname, Firstname (Dept) - if applicable. Note - not everyone has their dept name. Just if there is more then one person at the company with the same name.

I've found formula =IF(B2="","",IF(B2="N/A","N/A",MID(B2&", "&B2,FIND(" ",B2,FIND(" ",B2)+COUNTIF(B2,"* * *"))+1,LEN(B2)+1))) which will switch the names around and instead of Jane Doe (Sales) Changes it to Doe (Sales), Jane. this formula works for all the people without a department. How do I get it to show whatever is in parenthesis last?

thank you!

 

[jenny1013]

Do you want "Jane Ann Marie Smith" to become "Marie Smith, Jane Ann" (like "Jane Ann Doe Smith" became "Doe Smith, Jane Ann") or "Smith, Jane Ann Marie"?

Names always need attention by a human if they are to be handled correctly.

I'd say Marie Smith, Jane Ann. That fits most of the current odd balls and there might be a few that just have to get handled separately.

 

[jtakw]

I'd say Marie Smith, Jane Ann. That fits most of the current odd balls and there might be a few that just have to get handled separately.

I can see this may be an abyss of possible problems and endless questions, what if it's "Mary Jane E. Van Buren Jr. (Sales)" or ???
Sorry, I'm out.
If the formula you already have is working for you, may be consider 1 (or more) helper columns to separate the string, then concatenate the way you need.

Again, sorry I can't help further.

 

[mikerickson]

Named Formulas is one approach.
Select B1 and define these names

Name: RawCell RefersTo: =Sheet1!$A1

Name: Department RefersTo: =TRIM(MID(RawCell,FIND("(",RawCell&"("),255))
Name: RawName RefersTo: =TRIM(SUBSTITUTE(RawCell,Department,""))

Name: SpaceCount RefersTo: =LEN(RawName)-LEN(SUBSTITUTE(RawName," ",""))
Name: BreakPoint RefersTo: =FIND("~", SUBSTITUTE(RawName," ","~", ROUNDUP((SpaceCount+1)/2,0))&"~")

Name: First RefersTo: =TRIM(LEFT(RawName,BreakPoint))
Name: Last RefersTo: =TRIM(MID(RawName,BreakPoint,255))

Then the formula =Last&", "&First&" "&Department in a cell will return the result you want.