Identify pattern and extract substring

Question

I have thousands of rows in Excel where inside random text there are periods like "31.12.2019-30.12.2020".
I need to extract this substring from each row and put it in a separate cell.
The substring always has the same pattern but the text is different and it can be anywhere within the text.

Is it possible with a formula or through VBA to identify this pattern: "XX.XX.20XX-XX.XX.20XX" and then return the substring?

I couldn't find a formula through Google.

Answer 1

1) - Excel Formula

If you go the Excel Formula route, then use in B2:

=MID(A2,SEARCH("??.??.20??-??.??.20??",A2),21)

Drag down...

The SEARCH function does support wildcards such as ? (which stands for any character), and thus return the starting position of the substring of interest. When used in MID we can actually extract this substring.

---

2) - VBA

As per my comment above, this can also neatly be done through regular expressions (see link provided by @Warcupine). Regular expressions allow you to be more precise in your pattern (for example we can search digits instead of any char).

I can imagine you'd use an UDF where you can link to both text and your valid pattern, for example:

Public Function RegExtract(Txt As String, Pattern As String) As String

With CreateObject("vbscript.regexp")
    '.Global = True
    .Pattern = Pattern
    If .test(Txt) Then
        RegExtract = .Execute(Txt)(0)
    Else
        RegExtract = "No match found"
    End If
End With

End Function

You can call this in B2 through =RegExtract(A2,"\d\d\.\d\d\.20\d\d-\d\d\.\d\d\.20\d\d")

As you can see the pattern is pretty straightforward:

---

Results: