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

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
Asked:
snailcat
  • 2
3 Solutions
 
nutschCommented:
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
 
nutschCommented:
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
 
Saqib Husain, SyedEngineerCommented:
Try this formula

=RIGHT(A1,LEN(A1)-FIND(" ",A1))&IF(ISERR(FIND(",",A1)),",","")&" "&LEFT(A1,FIND(" ",A1)-2+ISERR(FIND(",",A1)))
0
 
barry houdiniCommented:
Doe, John to John Doe

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

regards, barry
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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