Placing a period after a middle initial

G

Guest

Guest
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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
 
Upvote 0
=IF(ISNUMBER(SEARCH(" ? ",A3)),LEFT(A3,SEARCH(" ? ",A3)+1)&"."&RIGHT(A3,LEN(A3)-SEARCH(" ? ",A3)-1),A3)
 
Upvote 0
God Bless You! That worked as needed.

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

Have a great day! Jonathan
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 -_-
 
Last edited:
Upvote 0
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
 
Upvote 0
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:)
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top