Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting using the RIGHT function (XP)

    What I am attempting to do is extra the information in the parenthesis in order to sort by job classification. I thought that the RIGHT Function might be suitable, but it gives me the workers first name along with the job classification. Is there a method of being able to extract what is in the parenthesis in order to do the sort?
    Attached Files Attached Files

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

    Re: Extracting using the RIGHT function (XP)

    Try

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

    in B1, and fill down as far as needed.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting using the RIGHT function (XP)

    Thanks Hans. This works well. When you have sometime would mind explaining the functioning of FIND("(" where it is used.

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

    Re: Extracting using the RIGHT function (XP)

    FIND(a, looks for the first occurrence of (the value of) a in (the value of) b, and returns the position of this first occurrence. So for example FIND("o", "Woody") returns 2 because the first occurrence of "o" in "Woody" is in the second character. And FIND("o", "Gates") returns an error because "o" is not found in Gates.

    The search is case-sensitive; if you want a case-insensitive search use SEARCH instead of FIND.

    If you look up FIND and SEARCH in the Excel help, you'll find more detailed information.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extracting using the RIGHT function (XP)

    Another option :

    =SUBSTITUTE(REPLACE(A1,1,FIND("(",A1),""),")","")

    Or this one, only in saving some keystrokes

    =SUBSTITUTE(REPLACE(A1,1,FIND("(",A1),),")",)

    Regards
    Bosco

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting using the RIGHT function (XP)

    Hi Bosco

    Is it working at your end? I tried the formula provided but its return #VALUE..... not sure why

    Thanks

    regards,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Extracting using the RIGHT function (XP)

    Are you sure that you entered the formula correctly? I have attached your workbook with both versions of bosco_yip's formula.
    Attached Files Attached Files

Posting Permissions

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