Extracting Text from a string of text between brackets

hi there,

Its probably best I outline an example and hoepfully you might be able to provide an answer to what I'm looking for

There is a name in A1 ........say its John

in A2 there are a number of names with a dollar amount in brackets to the right of each name.........for example.... Peter ($5000) Simon ($10000) John ($2000) Bob ($3000). I want to be able to look in cell A2 and find the dollar amount relating to the name in cell A1. So the result should be $2000......I have tried a combination of the following formulas

=MID(N1,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)

=MID(A2,FIND(RIGHT(A1),A2)+3,(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)))

but can't seem to get it......if you could please help that would be fantastic

I use excel 2007

Cheers

thanks for that....but there is no reference to A1 in your formula

cheers

Sorry, didn't read carefully

=0+MID(A2,FIND(A1,A2,1)+LEN(A1)+3,FIND(")",A2,FIND(A1,A2,1))-FIND(A1,A2,1)-LEN(A1)-3)

awesome

=LOOKUP(9.999999999E+307,--("0"&MID(MID(A2,SEARCH(A1,A2),255),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A2,SEARCH(A1,A2),255)&"0 123456789")),ROW($1:$30))))

That one appears to work a treat.........I don't understand a bit of it so way ask additional questions down the track if I run into issues.....thanks again

ah ha......what happens if the name in A1 doesn't appear in A2......how can I make it come up with ""? It appears to come up with a figure when it shouldn't

zbor,

that one doens't appear to work....it comes up with an error

that is returning "" when the name is found in the string of text.