• Status: Solved
• Priority: Medium
• Security: Public
• Views: 298

# Reverse names in Excel using VBA

I have some Excel files that have names listed either as last name, first name or first name last name in a cell.  How can I easily switch these around either using visual basic or a formula?

For example if cell A2 is Doe, John how can I make it John Doe?

Alternatively if I have A2 as John Doe how can I convert it to Doe, John?

I have a few very large spreadsheets that would need this done on column A for example.
0
snailcat
• 2
3 Solutions

Commented:
Formula would be

=left(a2, find(",",A2)-1)  & right(a2,len(a2)-find(",",a2))
for the first case

and

Assuming no middle names, middle initials or composed first or last names with spaces in the middle

=trim(right(a2,len(a2)-find(" ",a2))) &", " & left(a2, find(" ",A2)-1)

Thomas
0

Commented:
My bad on the first one, it doesn't change the order:

=RIGHT(A2,LEN(A2)-FIND(",",A2)-1) & " " & LEFT(A2, FIND(",",A2)-1)

T
0

EngineerCommented:
Try this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&IF(ISERR(FIND(",",A1)),",","")&" "&LEFT(A1,FIND(" ",A1)-2+ISERR(FIND(",",A1)))
0

Commented:
Doe, John to John Doe

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

regards, barry
0

## Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.