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!

 

[alansidman]

A simple way to do this is to highlight your data (assuming in Column A) and on the ribbon select Data-->Text to Columns---> Delimeted-->Check the box "Space" and set the destination to C1.

Now in A1 type =C1 &", " & B1 &" " & D1 and copy down. Then copy Column A and pastespecial values back to Column A. You can now delete columns B:D

 

[jenny1013]

This wouldn't be ideal. I'm looking for a way to do it all at once if possible. Someone else actually uses the report and they will copy and past the list for Firstname Lastname and have it automatically convert to the proper format so then can copy and paste the corrected list back on the report.

 

[erzon]

you can try this assuming your text is in A1

=CONCATENATE(MID(A1,FIND(" ",A1)+1,999),", ",LEFT(A1,FIND(" ",A1)))

 

[Rick Rothstein]

It is ugly, but I think this formula does what you want...

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

 

[Rick Rothstein]

It is ugly, but I think this formula does what you want...

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

Maybe this is a little less ugly...

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

 

[jtakw]

Hi,

According to your description, and your Existing formula.
1. Sometimes there is a Department within brackets--(Sales), but sometimes Not.
2. There may be Blank cells.
3. There may be cells with N/A.

I came up with this a few hours ago, but didn't have time to post it (was heading out):

Book1
	A	B
1	Jane Doe (Sales)	Doe, Jane (Sales)
2	Jane Doe	Doe, Jane
3
4	N/A	N/A
Sheet103

Cell Formulas
Range	Formula
B1	=IF(A1="","",IF(A1="N/A","N/A",IF(ISNUMBER(FIND("(",A1)),SUBSTITUTE(MID(A1,FIND(" ",A1)+1,255)," ",", "&LEFT(A1,FIND(" ",A1))),MID(A1,FIND(" ",A1)+1,255)&", "&LEFT(A1,FIND(" ",A1)-1))))

B1 formula copied down.

 

[jtakw]

Hi again,

Follow up from my post above, I also came up with a shorter/cleaner version of my formula:

Book1
	A	B
1	Jane Doe (Sales)	Doe, Jane (Sales)
2	Jane Doe	Doe, Jane
3
4	N/A	N/A
Sheet103

Cell Formulas
Range	Formula
B1	=IF(A1="","",IF(A1="N/A","N/A",TRIM(SUBSTITUTE(MID(A1&" ",FIND(" ",A1)+1,255)," ",", "&LEFT(A1,FIND(" ",A1)),1))))

Formula copied down.

 

[jenny1013]

Hi again,

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B1[/TH]
[TD="align: left"]=IF(A1="","",IF(A1="N/A","N/A",TRIM(SUBSTITUTE(MID(A1&" ",FIND(" ",A1)+1,255)," ",", "&LEFT(A1,FIND(" ",A1)),1))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

Hi jtakw – I’m trying to figure out how your formula works with the SUBSTITUTE. The formula works if it is Firstname Lastname (dept). The issue is if someone has a middle name/initial or a middle name/initial and two non-hypenated last names.

My formula above =IF(B2="","",IF(B2="N/A","N/A",MID(B2&", "&B2,FIND(" ",B2,FIND(" ",B2)+COUNTIF(B2,"* * *"))+1,LEN(B2)+1)))handles those issues.

The first column is the report. The middle column is your formula and the last column is the desired result.

[TABLE="width: 746"]
<colgroup><col width="80" style="width: 48pt;"><col width="333" style="width: 200pt;"><col width="333" style="width: 200pt;"></colgroup><tbody>[TR]
[TD]Jane Doe[/TD]
[TD]Doe, Jane[/TD]
[TD]Doe, Jane[/TD]
[/TR]
[TR]
[TD]Jane Ann Doe[/TD]
[TD]Ann, Jane Doe[/TD]
[TD]Doe, Jane Ann[/TD]
[/TR]
[TR]
[TD]Jane Doe (Sales)[/TD]
[TD]Doe, Jane (Sales)[/TD]
[TD]Doe, Jane (Sales)[/TD]
[/TR]
[TR]
[TD]Jane Ann Doe (Sales)[/TD]
[TD]Ann, Jane Doe (Sales)[/TD]
[TD]Doe, Jane Ann (Sales)[/TD]
[/TR]
[TR]
[TD]Jane Ann Doe Smith[/TD]
[TD]Ann, Jane Doe Smith[/TD]
[TD]Doe Smith, Jane Ann[/TD]
[/TR]
[TR]
[TD]Jane Ann Doe Smith (Sales)[/TD]
[TD]Ann Doe Smith (Sales) , Jane[/TD]
[TD]Doe Smith, Jane Ann (Sales)[/TD]
[/TR]
</tbody>[/TABLE]

 

[mikerickson]

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.