Extract text from string that is specific length of characters

[Country_Calc_2]

I need to extract a specific naming code that is always 14 characters long and always starts with 4 numbers followed by a hyphen - but could appear anywhere in the cell and does not always have a delimiter

Extraction examples are in red below, with the desired result in column B.

Please let me know if it is possible.

A	B
17158|J1L-AM-JZGB|AM0010-301|2016-003858-33	2016-003858-33
2015-003086-28	2015-003086-28
TAK-931-2001|U1111-1192-7975|JapicCTI-163200	return a blank
B9991033|2018-000124-34|333-54456M	2018-000124-34
IRB00011256|NCI-2015-00498|SOL-14124-L	return a blank

 

[BigBeachBananas]

Try this.

Insert VBA into a module.

Function ExtractPattern(cell As Range) As String
    Dim regex As Object
    Dim matches As Object
    Dim match As Object

    Set regex = CreateObject("VBScript.RegExp")
    With regex
        .Global = True
        .IgnoreCase = True
        .Pattern = "\b\d{4}-\d{6}-\d{2}\b"
    End With

    Set matches = regex.Execute(cell.Value)

    If matches.Count > 0 Then
        Set match = matches(0)
        ExtractPattern = match.Value
    Else
        ExtractPattern = ""
    End If
End Function

Then insert this in cell B1 and copy it down.

=ExtractPattern(A1)

 

[Country_Calc_2]

This works great! Thanks so much.

 

[Phuoc]

Another way:

Book1
	A	B
1	17158|J1L-AM-JZGB|AM0010-301|2016-003858-33	2016-003858-33
2	2015-003086-28	2015-003086-28
3	TAK-931-2001|U1111-1192-7975|JapicCTI-163200
4	B9991033|2018-000124-34|333-54456M	2018-000124-34
5	IRB00011256|NCI-2015-00498|SOL-14124-L
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(MID(A1,SEARCH("|????-??????-??|","|"&A1&"|"),14),"")

 

[Country_Calc_2]

Thanks for the formula option as well. It is a little easier to understand to modify for similar but different situations (at least for me) than vba.

 

[BigBeachBananas]

Thanks for the formula option as well. It is a little easier to understand to modify for similar but different situations (at least for me) than vba.

The formula won't guarantee it's all numerical, only the same pattern. For example:

17158|J1KL-AM12AK-GB|AM0010-301|2016-003858-33

J1KL-AM12AK-GB

 

[Phuoc]

Or use UDF

Function ExtractPattern(s As String) As String
    Dim v As Variant
    For Each v In Split(s, "|")
        If v Like "####-######-##" Then
            ExtractPattern = v
            Exit For
        End If
    Next v
End Function

 

[BigBeachBananas]

Or use UDF

Function ExtractPattern(s As String) As String
    Dim v As Variant
    For Each v In Split(s, "|")
        If v Like "####-######-##" Then
            ExtractPattern = v
            Exit For
        End If
    Next v
End Function

Hi Phuoc,
Did you test your code? It's not rendering as expected.