Placing a period after a middle initial

Hello Folks!

I have a unique situation that requires me to find and place a period in a string after the middle name. For example, in cell A2, I have the test Jonathan J McGuire. I need the text to read Jonathan J. McGuire. I think I need to start with the Find formula but after that I'm coming up short.

Thanks in advance.

Jonathan McGuire

[Mark W.]

=LEFT(A1,SEARCH(" ? ",A1)+1)&"."&RIGHT(A1,LEN(A1)-SEARCH(" ? ",A1)-1)

[jonathanm]

Hello Mark!

That worked like a champ. The only issue is when they don't have a middle initial it returns the old Excel favorite #VALUE. I tried placing an If in front to no avail.

Thanks again for your time.

Jonathan McGuire

[Mark W.]

=IF(ISNUMBER(SEARCH(" ? ",A3)),LEFT(A3,SEARCH(" ? ",A3)+1)&"."&RIGHT(A3,LEN(A3)-SEARCH(" ? ",A3)-1),A3)

[jonathanm]

God Bless You! That worked as needed.

Again, thanks for taking your time to assist in these oddball issues.

Have a great day! Jonathan

[IML]

You may also be able to get away with

=SUBSTITUTE(A1," ",". ",2)

good luck.

Just saw the distinction Mark put in. His specically looks for a sole character. Very nice.

[ This Message was edited by: IML on 2002-02-25 10:50 ]

[Nostrovia69]

Hello, I'm trying to place a period after the entire name as follows:
Smith, James F

I would also like it to differenciate to not do so if the person does not have a middle initial.

[samfolds]

Can you be a little more specific ? Does your input look like :
Smith James F all the time?

And did you mean a comma (,) instead of a period (.).

Give us a little more details and we'll be happy to help.

Samfolds

PS : Welcome to the board :D

EDIT : Nvm about the comma thing -_-

[samfolds]

Ok I think I got a bit more of what you're saying.

Assuming your input (i.e. the names) are always of the form :

"Last Name", "Name" "Initial" (and this is in column A)

Copy in B1 :

=IF(mid(A1,LEN(A1)-1,1)=" ",A1 & ".",A1)

And drag down.

Hope this helps!

Samfolds

[Nostrovia69]

Sure, sorry.

The example: Smith, James F
is the entries that have a middle initial....there are also entries that do not have a middle initial but are set up the same way i.e. last name, first name
I would like to have the function differenciate between not having an initial and having one...I just want to make sure it doesn't place a period after the first name. I would like it to only place a period after the middle initial if the name contains it. Thanks