# Reorder First Name and Last Name

1. ## 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

2. Where are you "inputting" this name and where do you want the "output"?

3. into A1 and output into A1

if necessary into A1, output B1

4. 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

5. 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

6. 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

7. 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

8. ## Re: Reorder First Name and Last Name

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))

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1