• 540K Members
• 2,989 Online
• 644K Conversations
SOLVED

Highlighted
New Contributor

# Reverse name order

I used to do complicated macros years ago, but I either forgot something or the options have changed.  I  can still do some macros, but not edit a cell.  I have a list of names that are "Last name, First name" and I want to reverse it so that it is "First name Last name".  I intended to go to the comma, cut the last name from the front of the cell, go to the end of the cell and paste it, but once I go into the cell, I can't select the "goto" or the "find" function to be able to do that.

8 Replies
Highlighted
Solution

# Re: Reverse name order

You don't need a macro to do that.

This formula does the trick:

`=RIGHT(A1,LEN(A1)-SEARCH(",",A1)-1)&" "&LEFT(A1,SEARCH(",",A1)-1)`

You can even do that without using any formula!

By using the Flash Fill.

Hope that helps

Highlighted

# Re: Reverse name order

You may try this formula in B2, assuming the text you want to convert is in A2:
=RIGHT(A2,LEN(A2)-FIND(“,”,A2)+1)&” “&
LEFT(A2,LEN(A2)-FIND(“,”,A2)-1)
Highlighted

# Re: Reverse name order

@Twifoo   The formula didn't work.  I received a #VALUE! error.  I did copy and paste the formula, so I know I did not make a typo.

Another response from another trusted community member suggested using Flash Fill and that worked beautifully.

Highlighted

# Re: Reverse name order

The formula gave me a #VALUE! error (and I copied and pasted your formula); HOWEVER, your suggestion to use Flash Fill worked beautifully.  Thank you so much.

Highlighted

# Re: Reverse name order

I’m glad to know that you achieved your objective although the best response didn’t come from me. What matters most is that you were able to achieve your objective!

# Re: Reverse name order

@joni_CCD-8244

It seems that the names in your original data are not separated by a comma+space just space??

If so, try this formula instead:

`=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))&" "&LEFT(A1,SEARCH(" ",A1)-1)`

Hope that helps

Highlighted

# Re: Reverse name order

Hi,

I am trying to use your formula as well but the names I use are a bit more complex.

For the name: de Bakker Alexandra

I want to obtain Alexandra de Bakker

How can I do this?

Highlighted

# Re: Reverse name order

@nauwelaertsevelien

Hi,

In this case, try this formula:

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

Hope that helps

Related Conversations
Site name in Windows Explorer
Bas_B in SharePoint on
1 Replies
How to get the highest value PER name in list?
Dragon_Claw in Excel on
3 Replies