Forum Discussion

Copper Contributor

May 29, 2019

Solved

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, F...

excel

Haytham Amairah
May 29, 2019
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)

You can even do that without using any formula!
By using the https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7.

Hope that helps

Haytham Amairah

Silver Contributor

May 29, 2019

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)

You can even do that without using any formula!

By using the https://support.office.com/en-us/article/using-flash-fill-in-excel-3f9bcf1e-db93-4890-94a0-1578341f73f7.

Hope that helps

joni_CCD-8244

Copper Contributor

May 29, 2019

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.

Haytham Amairah
Silver Contributor
May 29, 2019
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
- nauwelaertsevelien
  Copper Contributor
  Jan 28, 2020
  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.
  - Haytham Amairah
    Silver Contributor
    Jan 30, 2020
    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