Extracting a value from between brackets

Hello everyone,

I have entries like this:

ABC Company (FR 456721)
BCD Company (UK 4521)
ACD Company (France) (FR 56778)

I would like to extract the code that is always two
letters and then from 1 to 9 figures from between the
brackets. The codes between brackets are always after
the company name. Some company names have two sets of
brackets such as the example above with France between
brackets so I need to ignore that.

Thanks very much for your valuable help,
James

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))>1,MID(LEFT(A1,LEN
(A1)-1),FIND("(",A1,FIND("(",A1)+1)+1,999),MID(LEFT(A1,LEN
(A1)-1),FIND("(",A1)+1,999))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hello everyone,
>
>I have entries like this:
>
>ABC Company (FR 456721)
>BCD Company (UK 4521)
>ACD Company (France) (FR 56778)
>
>I would like to extract the code that is always two
>letters and then from 1 to 9 figures from between the
>brackets. The codes between brackets are always after
>the company name. Some company names have two sets of
>brackets such as the example above with France between
>brackets so I need to ignore that.
>
>Thanks very much for your valuable help,
>James
>.
>

Works perfectly. Thanks Jason for your help.

James
>-----Original Message-----
>=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))>1,MID(LEFT(A1,LEN
>(A1)-1),FIND("(",A1,FIND("(",A1)+1)+1,999),MID(LEFT
(A1,LEN
>(A1)-1),FIND("(",A1)+1,999))
>
>HTH
>Jason
>Atlanta, GA
>
>>-----Original Message-----
>>Hello everyone,
>>
>>I have entries like this:
>>
>>ABC Company (FR 456721)
>>BCD Company (UK 4521)
>>ACD Company (France) (FR 56778)
>>
>>I would like to extract the code that is always two
>>letters and then from 1 to 9 figures from between the
>>brackets. The codes between brackets are always after
>>the company name. Some company names have two sets of
>>brackets such as the example above with France between
>>brackets so I need to ignore that.
>>
>>Thanks very much for your valuable help,
>>James
>>.
>>
>.
>

On Thu, 3 Feb 2005 08:47:43 -0800, "James"
<[email protected]> wrote:

>Hello everyone,
>
>I have entries like this:
>
>ABC Company (FR 456721)
>BCD Company (UK 4521)
>ACD Company (France) (FR 56778)
>
>I would like to extract the code that is always two
>letters and then from 1 to 9 figures from between the
>brackets. The codes between brackets are always after
>the company name. Some company names have two sets of
>brackets such as the example above with France between
>brackets so I need to ignore that.
>
>Thanks very much for your valuable help,
>James

If the relevant code is the only thing that has the pattern of a bracket
followed by two characters and a <space>, then:

=MID(A1,SEARCH("(?? ",A1)+1,SEARCH(")",
MID(A1,SEARCH("(?? ",A1)+1,255))-1)


--ron