Home » Blogs » Nick's blog

59. Excel Tips - Reverse a name

Nick's picture
Submitted by Nick on 9 March, 2009 - 11:23


How do you reverse a name in Excel ?

This tip is very useful if you have a list of names in "Surname FirstName" format and you want to reverse that quickly.

Well, in Excel, there are a set of useful string manipulation functions you can use for this: LEN, FIND, MID, LEFT, RIGHT

  • LEN - Returns the number of characters in a value
  • FIND - Returns the position of a value in another value
  • MID - Extracts a value based on a starting position and length
  • LEFT - takes the X left most characters from a value
  • RIGHT - takes the X right most characters from a value ... where X is a number With the cell containing the name in B3.

First, we find the space.

=FIND(" ",B3,1)

Once we know the position of the space, we can reconstruct the name to reverse it by taking everything to the right of the space and adding everything to the left.

Extract Second Name

=LEFT(B3,FIND(" ",B3,1))

Extract First Name

=RIGHT(B3,LEN(B3)-FIND(" ",B3,1))

 

Here's how it looks in Excel: reverse-name-excel Download sheet to practise how to reverse a name in excel

 

Video Training on how to reverse a name:

 

»

Reverse Last Name, Name, Middle Name

Submitted by erickton on 29 April, 2013 - 06:24.

Try this, it works for Last Name, Name OR Last Name, Middle Name, Name:

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

»

reversing names

Submitted by tonto on 7 August, 2014 - 00:43.

I tried your formula original format was Jay M Hall this formula produced this format: M Hall Jay
I need it to be in this format: Hall, Jay M
Sometimes there is a period after the middle name and sometimes there is no middle name. I need to use this formula to work on all name formats as I am using a list of 1000 names.

»

Thanks

Submitted by timtak on 30 November, 2012 - 01:19.

Thank you.

And for folks in a rush, the forumual to concatenate the two, now reversed, names is, as in the downloadable sheet
=E3&" "&D3
Or
=CONCATENATE(E3," ", D3)

(By the way, minor point, it says "please click post button to confirm your post" but the button is a "save" button)

»

Reverse names

Submitted by Anonymous on 13 July, 2012 - 21:32.

Thank you for this tutorial. We used it to day and only took a few minutes to reverse the names of about 164 employees so we could cross reference in one part of our system. It cut the work down tremendously.

»