Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    picking info from a cell (xp or 2003)

    I would like to have a formula which will give me only part of the contents of a cell.

    If the cell content has MONTHLY ACCR OTPS/O-ACCR (271) or LEGAL /O-ADM-67307801 (85) . Is there a way to write a formula to yield:

    MONTHLY ACCR OTPS/O-ACCR or LEGAL /P-ADM-67307801

    no space (271) or space (85)

    Thanks

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

    Re: picking info from a cell (xp or 2003)

    Say that the values are in column A, starting in A1.
    In B1, enter the formula
    <code>
    =LEFT(A1,FIND(" (",A1)-1)
    </code>
    and fill down as far as needed. This assumes that the text can be truncated at the first " (" encountered.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: picking info from a cell (xp or 2003)

    That works great. I see I didn't think through the whole project. Is there a way to left justify the results. I am including a spread shee to show the inital data and what the final data should look like.

    Thanks,

    MNN

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

    Re: picking info from a cell (xp or 2003)

    If it's acceptable to replace multiple spaces within an entry with single spaces, you can use the TRIM function:

    =TRIM(LEFT(A5,FIND(" (",A5)-1))

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: picking info from a cell (xp or 2003)

    Thank you, It works great.

    What does the -1 do at the end of the formula?


    Thanks again

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: picking info from a cell (xp or 2003)

    The -1 reduces the length so that it does not include the parenthesis the the Find function found.
    Legare Coleman

Posting Permissions

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