Extract code of certain length from text string?

[Mndlssbrndlsm]

Hi, I hope someone can help with the MS Excel query below

I have a list of stock names and their ISIN codes all in one column. I would like to extract all of these codes into a separate column for use with a VLOOKUP. The data looks like this:

BIG COMPANY PLC GB00B7980591 ORD
USA CO INC (US0006880591) COM
DE0007150308 - GERMAN INDUSTRIES AG


The company names will all have a different length and number of words. The ISIN codes are not located in a uniform position; i.e they could be at the beginning, middle or end of the cell. Some of the ISIN codes are in parenthesis and some are not. ISIN codes are all the same length and all begin with the country of issue as the first two letters.

Does anyone know of a formula or VBA script capable of extracting this data?

Thanks in advance for any replies!

 

[MickG]

Try this:-
Results in "B"

[COLOR="Navy"]Sub[/COLOR] MG01Oct14
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] strg [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    strg = Replace(Replace(Dn, "(", ""), ")", "")
    Ray = Split(strg, " ")
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Ray
            [COLOR="Navy"]If[/COLOR] Len(R) = 12 And Not IsNumeric(Left(R, 1)) And IsNumeric(Right(R, 1)) [COLOR="Navy"]Then[/COLOR]
                Dn.Offset(, 1) = R
                [COLOR="Navy"]Exit[/COLOR] For
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mndlssbrndlsm]

That works perfectly! Thanks Mick!