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"
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