Mid Function, Substitute Combination (XP)

Topic

New Reply

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.

Reply | Quote

Replies

In cell C2, enter the formula

=LEFT(A2,SEARCH("(",A2)-2)

In cell D2, enter

=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2,SEARCH("(",A2)+1)-SEARCH("(",A2)-1)

In E2, enter

=MID(A2,SEARCH(")",A2,SEARCH("(",A2)+1)+2,100)

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.

Reply | Quote

Thanks so much.

Reply | Quote

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,”)”,”(“),”(“,REPT(” “,50)),COLUMN(A:A)*50-49,50))

Regards
Bosco

Reply | Quote