May 29 2019 07:49 AM
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.
May 29 2019 08:36 AM
SolutionHi @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 Flash Fill.
Hope that helps
May 29 2019 08:36 AM
May 29 2019 09:37 AM
@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
May 29 2019 09:46 AM
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.
May 29 2019 10:03 AM - edited May 29 2019 10:05 AM
May 29 2019 10:09 AM
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
Jan 28 2020 01:22 AM
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.
Jan 30 2020 11:23 AM
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
May 29 2019 08:36 AM
SolutionHi @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 Flash Fill.
Hope that helps