Reorder First Name and Last Name

I would like to type in the following:

First Name Middle Initial (or Name) Last Name (with or without the "." after the Middle Initial (if no Name))

and output the following:

Last Name, First Name Middle Initial (or Name)

Any suggestions?

Thanks

Justin

into A1 and output into A1

if necessary into A1, output B1

In the same cell would need a macro.

He's a formula to but into cell B2

=SUBSTITUTE(RIGHT(TRIM(A1),LEN(TRIM(A1))
-IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1),1)+1)),FIND(" ",TRIM(A1),1),FIND(" ",TRIM(A1),FIND(" ",TRIM(A1),1)+1)))
&", "&
IF(ISERROR(MID(A1,FIND(" ",A1)+1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)), FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+ 1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))
&" "&
LEFT(TRIM(A1),FIND(" ",TRIM(A1),1)-1),".","",1)

VBA Noob

Whoops

Wrong order. Should be

Entered with Alt + enter to add a new line. You should be able to paste it directly into the formula bar.

=SUBSTITUTE(RIGHT(TRIM(A1),LEN(TRIM(A1))
-IF(ISERROR(FIND(" ",TRIM(A1),FIND(" ",TRIM(A1),1)+1)),FIND(" ",TRIM(A1),1),FIND(" ",TRIM(A1),FIND(" ",TRIM(A1),1)+1)))
&" "&
LEFT(TRIM(A1),FIND(" ",TRIM(A1),1)-1),".","",1)
&", "&
IF(ISERROR(MID(A1,FIND(" ",A1)+1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)), FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+ 1,IF(ISERROR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))

VBA Noob

holy smokes, if the entire name is in a single cell there is no way to just format the cell using a custom format?

then it would be best if the names were in seperate cells?
if so, how would you combine them into the format:

Smith, A. John

Hi,

If A1 has John A. Smith I would suggest

Data > Text to columns > delimiter > space.

Then in D1 enter

=IF(B1="",TRIM(C1&", "&A1),TRIM(C1&", "&B1&" "&A1))

VBA Noob

Try a few functions together. I just learned this today and then found this post right before I figured it out.
CONCATENATE combines things together, RIGHT gives the text values from the right side and LEN is placed to give a total count of the cell's values, and FIND returns the number placement (counting from the left) where the comma could be found.


This formula is if my name was as such{ Frankenstein, Paul } Found in CELL L14, but I wanted to be returned as { Paul, Frankenstein }.

Put cursor in different cell, and type the following.
=CONCATENATE(RIGHT(L14,(LEN(L14)-FIND(",",L14))),", ",LEFT(L14,FIND(",",L14)-1))


Re-order your name.JPG