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.

[regular expressions](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) — Warcupine, Apr 30 '20 at 14:42

JvdV · Accepted Answer · 2020-04-30T15:15:57.273

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:

Regular expression visualization

Results:

score 0 · Answer 2 · answered Apr 30 '20 at 15:37

0

Another formula solution.

In B1, formula copied down :

=MID(A2,FIND("-",A2)-10,21)

answered Apr 30 '20 at 15:37

bosco_yip

3,762
2
5
10

1

What about compounded words in text: "dry-cleaning", "one-half", "mother-in-law" etc etc. – JvdV May 01 '20 at 06:14

Identify pattern and extract substring

2 Answers2