Convert the order the first and last name is written via formula

polska2180

Active Member
Joined
Oct 1, 2004
Messages
384
Office Version
  1. 365
Hi assuming I have

"Driver Name: Donald Williams Jr" in Cell A1 in B1 I am looking formula that will show "Williams Jr, Donald"

...not sure if that is possible so the other option is in B1 have just "Donald Williams Jr" then in C1 "Williams Jr, Donald"

thanks for the help.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
How about
=MID(B1,FIND(" ",B1)+1,LEN(B1))&", "&LEFT(B1,FIND(" ",B1)-1)

edit:- think I might have misread it, I didn't notice that the text, "Driver Name" was in the same cell.
 
Last edited:
Upvote 0
If you combine those two formulas in B1, you'd have this mess:

Code:
=MID(MID(A1,FIND(": ",A1)+2,50),FIND(" ",MID(A1,FIND(": ",A1)+2,50))+1,50)&", "&LEFT(MID(A1,FIND(": ",A1)+2,50),FIND(" ",MID(A1,FIND(": ",A1)+2,50))-1)
 
Upvote 0
Correcting my previous error, slighly shorter than the combined version above, but not by much.

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",3))+1,LEN(A1))&", "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*2,LEN(A1)))
 
Upvote 0
Taking a different approach to the combined formula, this one looks like it works.

=SUBSTITUTE(MID(A1&", "&A1,FIND(" ",A1,14)+1,LEN(A1)+1),"Driver Name: ","")
 
Upvote 0
Let me put in my option:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,14))&", "&MID(A1,14,SEARCH(" ",A1,14)-14)

So I assume it always starts "Driver Name: " so I know the first name starts at position 14.

The challenge is with people who use a middle name or honorifics.

[TABLE="width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Driver Name: Donald Williams Jr[/TD]
[TD]Williams Jr, Donald[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Driver Name: Donald Williams[/TD]
[TD]Williams, Donald[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Driver Name: Donny Boy Williams[/TD]
[TD]Boy Williams, Donny[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Driver Name: Mary Lou Williams[/TD]
[TD]Lou Williams, Mary[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Driver Name: Mr Williams[/TD]
[TD]Williams, Mr[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Driver Name: Donald Arthur Williams III[/TD]
[TD]Arthur Williams III, Donald[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Driver Name: Sir Donald Williams, Esq[/TD]
[TD]Donald Williams, Esq, Sir[/TD]
[/TR]
</tbody>[/TABLE]
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,14))&", "&MID(A1,14,SEARCH(" ",A1,14)-14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
Another option

=REPLACE(A1,1,FIND(" ",A1,14),"")&", "&MID(A1,14,FIND(" ",A1,14)-14)
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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