Reputation:

Insert space before middle initial name

In column A I have a list of names. For example in cell A1 I have:

Johnson,BenE.

The "E." is the middle initial part of the name. I needed to insert a space so that it looks like

Johnson,Ben E.

I can't seem to figure out the proper formula for it.

Upvotes: 0

Answers (3)

Reputation: 8422

Assuming the middle initial is always one letter followed by a period

=LEFT(A1,LEN(A1)-2)&' '&RIGHT(A1,2)

Upvotes: 0

Reputation: 60224

And, to make use of a rarely used function:

=REPLACE(A1,LEN(A1)-1,0," ")

Upvotes: 1

Reputation:

=LEFT(A1,SEARCH(".",A1)-2) &" " & RIGHT(A1,2)

Got it!

Upvotes: 0