Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Placing a period after a middle initial

This is a discussion on Placing a period after a middle initial within the Excel Questions forums, part of the Question Forums category; Hello Folks! I have a unique situation that requires me to find and place a period in a string after ...

  1. #1
    Guest

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654

    Default

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

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    2

    Default

    God Bless You! That worked as needed.

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

    Have a great day! Jonathan

  6. #6
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    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 ]

  7. #7
    New Member
    Join Date
    Jun 2010
    Posts
    4

    Default Re: Placing a period after a middle initial

    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.

  8. #8
    Board Regular
    Join Date
    Jul 2009
    Posts
    191

    Default Re: Placing a period after a middle initial

    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 by samfolds; Jun 10th, 2010 at 10:58 AM.

  9. #9
    Board Regular
    Join Date
    Jul 2009
    Posts
    191

    Default Re: Placing a period after a middle initial

    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

  10. #10
    New Member
    Join Date
    Jun 2010
    Posts
    4

    Default Re: Placing a period after a middle initial

    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

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com