Extracting a value from between brackets

J

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
 
J

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
 
R

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
Click to expand...

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
 
Top