# Thread: Mid Function, Substitute Combination (XP)

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

2. ## 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. ## Re: Mid Function, Substitute Combination (XP)

Thanks so much.

4. ## 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
•