Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Placing a period after a middle initial

  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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 09:58 AM.

  9. #9
    Board Regular
    Join Date
    Jul 2009
    Posts
    191
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

Some videos you may like

User Tag List

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
  •