Need help showing department names in ()

jenny1013

New Member
Joined
Feb 12, 2018
Messages
7
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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
 
Upvote 0
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.
 
Upvote 0
you can try this assuming your text is in A1

=CONCATENATE(MID(A1,FIND(" ",A1)+1,999),", ",LEFT(A1,FIND(" ",A1)))
 
Upvote 0
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))
 
Last edited:
Upvote 0
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)
 
Upvote 0
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
AB
1Jane Doe (Sales)Doe, Jane (Sales)
2Jane DoeDoe, Jane
3
4N/AN/A
Sheet103
Cell Formulas
RangeFormula
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.
 
Upvote 0
Hi again,

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


Book1
AB
1Jane Doe (Sales)Doe, Jane (Sales)
2Jane DoeDoe, Jane
3
4N/AN/A
Sheet103
Cell Formulas
RangeFormula
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top