Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: isolating initials from name in single cell

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In A1 I have a name, "Bob Smith".
    In B1, I'd like a formula that creates "B.S." I know it involves using MID, FIND, in some delicious mixture of commas and parentheses and concatenation.

    I'd also like to get a handle on the formulas to put the "B" in C1 and the "S" in D1.

    Thanks for the help.
    Regards, Duane
    Office2010 in Win7

  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)&"."&MID(A1,FIND(" ",A1)+1,1)&"." produces "B.S." in cell B1

    =LEFT(B1) produces "B"

    =LEFT(RIGHT(B1,2)) produces "S"


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Splendid, thanks much!
    Regards, Duane
    Office2010 in Win7

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-17 14:24, Duane wrote:
    In A1 I have a name, "Bob Smith".
    In B1, I'd like a formula that creates "B.S." I know it involves using MID, FIND, in some delicious mixture of commas and parentheses and concatenation.

    I'd also like to get a handle on the formulas to put the "B" in C1 and the "S" in D1.

    Thanks for the help.

    Consist your data solely of

    FirstName followed by a Space followed by Lastname?


  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually Mark (et al),

    I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).

    And Aladin, yes, the format in A1 will always be FirstName LastName.

    But, were you preparing to propose formulas incase the name in A1 was not in that format?
    Please do!

    Thanks again...
    Regards, Duane
    Office2010 in Win7

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,647
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-17 18:39, Duane wrote:
    Actually Mark (et al),

    I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).

    And Aladin, yes, the format in A1 will always be FirstName LastName.

    But, were you preparing to propose formulas incase the name in A1 was not in that format?
    Please do!

    Thanks again...
    In B1 enter:

    =LEFT(A1)&"."

    In C1 enter:

    =MID(A1,SEARCH(" ",A1)+1,1)&"."

    Instead of posting the forgoing, I replied with that question you quoted above:

    In D1 enter:

    =IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=2,MID(A1,SEARCH("@",SUBSTITUTE(A1," ","@",2))+1,1)&".","")

    You need more to capture the initials of some e.g., "catholic" names.

    Names including titles, particles like Jr., etc. go untreated. And, some names are not covered at all, e.g.:

    Marie-France Tardieu becomes from above M. and T.;
    P. N. Johnson-Laird --> P. N. J.
    Paul van Doorn --> P. v. D.

    Aladin


  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks much!!
    Regards, Duane
    Office2010 in Win7

  8. #8
    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

    On 2002-04-17 18:39, Duane wrote:
    Actually Mark (et al),

    I'd like the b and the s (the first initial of first and last names) formula to be pulling from the A1 cell that has the full name in it, not from the B1 (so I can use either method).
    Too easy...

    If A1 contains "Bob Smith" you can select B1:C1 and enter the following array formula...

    {=MID(A1,{0,1}*FIND(" ",A1)+1,1)}

    ... to return "B" in cell B1 and "S" in cell C1.

    Note: As mentioned this is an array formula which must be entered using the Control+Shift+Enter key combination. For more on array formulas see the Excel Help Index topic for "About array formulas and how to enter them".

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Fort McMurray, AB
    Posts
    218
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey Mark,

    Thanks for this. Yeah, array formulas. MrExcel calls them "CSE" formulas to remind us to use control-shift-enter when entering them.

    Good show, mate!
    Regards, Duane
    Office2010 in Win7

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
  •