Extract multiple values from cell string

[Lilia]

Hello all,
I need to extract specific values (Project numbers) from a Transaction Description column and create a separate column with the Project numbers by itself.
Note that the description column is not uniformed, Proj No can show in the beginning, middle or end of the string.
The only consistent data is the a Proj No is an 8 digit figure and start with the year. I also have a list of projects in case it makes the search easier.
I tried using conditional formatting to to color each set of projects a different color and filter them, but no luck....
Help please....
Eg. of the Transaction Description Column:

#74315# 20130108-WO9-R1-5/31/16
*745* 20150079-R5-6/27-8/14/16
ADPICS PURCHASE ORDER RETAINAGE-> CVMT16000713
ADPICS RETAINAGE BACKOUT FOR VOUCHER CANCEL
ADPICS PURCHASE ORDER RETAINAGE-> CVMT17000004
ADPICS PURCHASE ORDER RETAINAGE-> CVMT17000027
ADPICS PURCHASE ORDER RETAINAGE-> CVMT17000069
*745* 20150079-R5-6/27-8/14/16
*15-137-04* 20150145-R4-8/1-8/28/16
*744* 20150085-R5-6/20-7/17/16

 

[bosco_yip]

Try,

In B1, copy down :

=IFERROR(LOOKUP(9^9,--MID(A1,ROW(INDIRECT("1:"&LEN(A1)-8)),9)),"")

Regards
Bosco

 

[Lilia]

It didn't work for all rows, see attached.
I had also used MID(K18,26,8) but only works if Proj numbers are in the same position.

 

[bosco_yip]

See attached revised file

Regards

 

[Lilia]

Hi bosco_yip
Thanks so much for your help, see additional examples pasted to the file in which the formula does not apply entirely.
The file I'm working with has over 190,000 records, that's why as I scroll down I come up with more variables.

 

[Chihiro]

I'd recommend using UDF in such a case.

Open VBE by hitting Alt + F11. Insert Module and paste following.

Public Function prjExtract(sRange As Range) As Variant
Dim RegEx As Object, sMatch As Object
Set RegEx = CreateObject("VBScript.RegExp")

With RegEx
    .Pattern = "[2]\d{7}"
    .Global = False
End With

If RegEx.Test(sRange.Value) Then
    Set sMatch = RegEx.Execute(sRange.Value)
    prjExtract = CLng(sMatch(0))
Else
    prjExtract = ""
End If

End Function

In T7:
=prjExtract(K7)

Copy down.

 

[Lilia]

Thank you so much, it worked