Extract text in last paranthesis

[Parre]

Hi.

I'm trying to extract a number formatted as text from within paranthesis.

The text is formatted like this:
"textexample (11-2222-33)"

I'm using the formula:
=SUBSTITUTE(MID(B1;FIND("(";B1)+1;255);")";"")

The output is "11-2222-33" as expected but some of the cells contains two paranthesis as following:

"textexample (aa) (11-2222-33)"

My formula outputs:
"aa (11-2222-33"

How can i modify the formula to take an extra pair of paranthesis in consideration and make the output the same as in the first example?

/Parre

 

[Andrew Poulsom]

Try:

=SUBSTITUTE(MID(B1,FIND("!",SUBSTITUTE(B1,"(","!",LEN(B1)-LEN(SUBSTITUTE(B1,"(",""))))+1,255),")","")

 

[Parre]

Works great.

Thanks!

 

[Peter_SSs]

Is the text of interest always at the right and/or always 10 characters in length?

Would either of these work for you?

=MID(A1,LEN(A1)-10,10)

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),"(",REPT(" ",100)),100))

 

[Parre]

The text is always at the right, but sometimes there is a "space" between the last number and the last paranthesis that also has to be removed.

exampletxt (11-2222-33 )


Andrews formula and your second formula corrects for this.

/Parre

 

[Peter_SSs]

The text is always at the right, but sometimes there is a "space" between the last number and the last paranthesis that also has to be removed.

exampletxt (11-2222-33 )

In that case, and if the number of characters is always 10, then this might do:

=MID(A1,LEN(A1)-10-(RIGHT(A1,2)=" )"),10)