Extracting a value from between brackets

[James]

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

 

[Jason Morin]

=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

 

[James]

Works perfectly. Thanks Jason for your help.

James

 

[Ron Rosenfeld]

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