Reputation: 1
Good day, I'm using the following formula to alter a list of first/last names. The result of the formula works well for me except I would like a space between the first name and the initial of the last name. How can I add the space into my existing formula?
=RIGHT(A5,LEN(A5)-FIND("*",SUBSTITUTE(A5,", "," * ",LEN(A5)-LEN(SUBSTITUTE(A5," ","")))))& LEFT(A5)&"."
Names initially entered in my spreadsheet:
Doe, John
Doe, Jane
Result of formula entered: JohnD. JaneD.
This is how I would like to see the names appear in my spreadsheet:
John D.
Jane D.
Any help would be appreciated.
Upvotes: 0
Views: 440
Reputation: 3960
This should do it:
=RIGHT(A5,LEN(A5)-FIND("*",SUBSTITUTE(A5,", "," * ",LEN(A5)-LEN(SUBSTITUTE(A5," ","")))))& " " & LEFT(A5)&"."
Upvotes: 2