SOLVED

Reverse name order

Copper Contributor

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
best response confirmed by joni_CCD-8244 (Copper Contributor)
Solution

Hi @joni_CCD-8244,

 

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)

 Reverse name order.png

 

You can even do that without using any formula!

By using the Flash Fill.

 

Hope that helps

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)

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

Thank you for your time

@Haytham Amairah 

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.

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!

@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

@Haytham Amairah 

 

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 receive: Bakker Alexandra de with your formula.

I want to obtain Alexandra de Bakker

 

How can I do this?

 

Thank you for your help.

@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

1 best response

Accepted Solutions
best response confirmed by joni_CCD-8244 (Copper Contributor)
Solution

Hi @joni_CCD-8244,

 

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)

 Reverse name order.png

 

You can even do that without using any formula!

By using the Flash Fill.

 

Hope that helps

View solution in original post