Formulas in

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:38 PM GMT

Question

I have a list of names- last name, first name

I need to change the order to - first name last name

I have used a formula (probably for names with middle initial included) that changes the order but, there is an extra space between the names. Is there another formula to use for names without the middle initial? This is the formula I am using:=RIGHT(A19,LEN(A19)-LEN(LEFT(A19,FIND(",",A19)-1))-2)&""&LEFT(A19,FIND(",",A19)-1)

I'd appreciate your help.

Answer

Please forgive my slow response. I have been on vacation and forgot until now to check my question inbox since I returned on April 1.

I did modify the formula slightly:

=RIGHT(A21,LEN(A21)-LEN(LEFT(A21,FIND(",",A21)-1))-2)&" "&LEFT(A21,FIND(",",A21)-1)

but actually added a space to it instead of deleting one. I believe the problem you encountered can only be explained by having a space in front of the "last name, first name" string. Here are the results I get with the formula above:

Case Input Formula Result

 1  "Newman, Alfred E"         "Alfred E Newman"
 2  " Newman, Alfred E"        "Alfred E  Newman"   (extra space)
 3  "Newman,  Alfred E"        " Alfred E Newman"
 4  "Newman, Alfred E."        "Alfred E. Newman"
 5  "Newman, Alfred"           "Alfred Newman"
 6  " Newman, Alfred"	        "Alfred  Newman"     (extra space)
 7  "Newman,  Alfred" 	        "Alfred Newman"


I believe this is what you actually want, bu as you can see, you get an extra space between the first and last name if there is a space at the beginning of the string in the input cell. I suggest you check to make sure your input strings have no spaces at the front.

Feel free to follow up if I have missed something.

Advertisement

©2022 eLuminary LLC. All rights reserved.