Excel 2003 - Separating Text in brackets

[Silver Fox]

Hi,


Apols - I'm struggling with this!

I've got one column containing data and which I would like to separate into two columns.
The data looks like this:-
017A - Sitting/Rest Room (Sitting Room 10sq m (SF) inc TV)
What I'm looking for is a way of saying wherever you see the first open bracket and the last closed bracket, place that text into the adjacent (right hand) column.

Thanks for your help - best wishes

 

[ukmikeb]

Hi

Try -

=MID($A41,FIND("(",$A41)+1,FIND("#",SUBSTITUTE($A41,")","#",LEN($A41)-LEN(SUBSTITUTE($A41,")",""))))-(FIND("(",$A41)+1))

Changing A41 to your cell.

Formula caters for characters present after last right-hand bracket.

hth

 

[Silver Fox]

Thanks - it worked perfectly - unfortunately due to more haste, less speed on my behalf I had given some duff info.
What I wanted was to find the data (text) and cut and paste it into the next column on the right.
Many apologies

 

[ukmikeb]

Hi

Place the original formula in column C.

Place the following formula in column B -

 =SUBSTITUTE($A41,"("&$C41&")","")

obviously changing the cell references (NB it also removes first and last brackets).

Then when you have completed do a Copy and Paste Special - Paste Values only - for columns B and C .

And finally, delete Column A.

hth

 

[Silver Fox]

Mike,

Very many thanks for your help on this,
Best regards,

 

[Sunny]

Mike

Just had (what would have been) a mammoth task - but thanks to your help it hasn't taken a 10th of the time.
Once again, many thanks,

 

[Peter_SSs]

Sounds like the task is long ago done but if the original text always ends with ")" (I'm not sure it does as we only have 1 example to go on) then this would be simpler to get the value in C41:

=REPLACE(LEFT(A41,LEN(A41)-1),1,FIND("(",A41),"")

 

[ukmikeb]

Sunny

Pleased to have helped you save a lot of time on your project.

Pleased to also see that some users are using the Search facility to arrive at solutions to their problems.