Downer
Downer

Reputation: 1

Adding a space to a formula

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

Answers (1)

sous2817
sous2817

Reputation: 3960

This should do it:

=RIGHT(A5,LEN(A5)-FIND("*",SUBSTITUTE(A5,", "," * ",LEN(A5)-LEN(SUBSTITUTE(A5," ","")))))& " " & LEFT(A5)&"."

Upvotes: 2

Related Questions