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

    Mid Function, Substitute Combination (XP)

    I have been given a large spreadsheet to clean up. In the attached you can see what I received and how client would like it to be. Is there a method to extract each component as what I have shown in the What I want section? Have tried various combinations of LEFT,MID, SUBSTITUTE etc. but cannot get what I want. Ideally, but not absolutely necessary , is it then possible to have the original column deleted?

    Thanks.
    Attached Files Attached Files

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

    Re: Mid Function, Substitute Combination (XP)

    In cell C2, enter the formula
    <code>
    =LEFT(A2,SEARCH("(",A2)-2)
    </code>
    In cell D2, enter
    <code>
    =MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2,SEARCH("(", A2)+1)-SEARCH("(",A2)-1)
    </code>
    In E2, enter
    <code>
    =MID(A2,SEARCH(")",A2,SEARCH("(",A2)+1)+2,100)
    </code>
    Select C2:E2, then fill down as far as necessary.
    With the range still selected, click the Copy button on the toolbar.
    With the range still selected, click the little dropdown arrow to the right of the Paste button, and select Values.
    You have now replaced the formulas with their values, so you can safely delete column A.

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

    Re: Mid Function, Substitute Combination (XP)

    Thanks so much.

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

    Re: Mid Function, Substitute Combination (XP)

    Or, you can use one(1) formula to complete the work

    Cell C2, enter the formula , copy across to E2

    and, select C2:E2, copy down

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A2,")","("),"(",R EPT(" ",50)),COLUMN(A:A)*50-49,50))

    Regards
    Bosco

Posting Permissions

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