Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Extract Text ? (Excel 2002)

    Hi

    I have been able to extract salutation from C2 but I can't select the Initial.

    Please see sample attached.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    You could use:

    <code>=MID(C2,FIND(" ",C2)+1,1)</code>

    This will allow for salutaions longer than 2 characters, if the initial is always the 4th character then you could use:

    <code>=MID(C2,4,1)</code>

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Braddy

    In addition to Tony's answer to find an initial from the string use:

    =MID(C2,LEN(LEFT(C2,FIND(" ",C2)+1)),1)

    This wiil work for all titles: Mr Mrs, Miss, Prof. etc
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text ? (Excel 2002)

    LEN(LEFT(C2,x)) = x by definition of the functions involved. So there is no need to use LEN and LEFT.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Yep...thanks <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Jerry

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Hi Tony

    Thanks for the reply, I have discovered a problem with my formula in A2 it only brings in Mr where it will need to bring in Mrs Or Miss in some cases.

    If I could impose on you, after I Have in A2 Mr and B2 D then I would like to be able to remove them from C2 leave just Stephens.

    Thanks to all who replied

    Braddy
    If you are a fool at forty, you will always be a fool

  7. #7
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Salutation

    <code>=LEFT(C2,FIND(" ",C2)-1)</code>


    Initial

    <code>=MID(C2,FIND(" ",C2)+1,1)</code>


    Surname

    <code>=RIGHT(C2,LEN(C2)-FIND("*",SUBSTITUTE(C2," ","*",LEN(C2)-LEN(SUBSTITUTE(C2," ","")))))</code>


    (I think I got the last method from a John Walkenbach book)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Text ? (Excel 2002)

    If you have salutations consisting of more than one word such as "Mr or Ms" and "Prof Dr" etc., it is hard to do with a formula, because you cannot just search for the first space. Also, you cannot use a formula to change C2 itself, because the moment you do that, A2 and B2 will become invalid. So it would be better to use VBA code, but you will need to provide a complete list of all possible salutations.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Hi Tony

    Thanks very much, everything works just fine,

    Once again thanks to everyone who replied.

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text ? (Excel 2002)

    For middle name put (checks to see whether there is a middle name):

    =IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Extract Text ? (Excel 2002)

    Hi Shades

    Took my breath away a bit, but I will give it a go.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  12. #12
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Missouri, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Text ? (Excel 2002)

    I think it comes from J. Walkenbach.

    I know I use it frequently.

Posting Permissions

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