Some text can't be cleaned by position or by a fixed replacement: a memo that says "payment for inv 1042 thanks", or "INV-1042", or "Invoice #1042" depending on who typed it. A regular expression describes the SHAPE of what you want — the letters I-N-V, maybe a dash or a space or a hash, then digits — and finds it wherever it is and however it's written. VBA reaches regular expressions through the VBScript library, created late-bound so it runs on any Windows machine without a reference. It's powerful and unforgiving: a pattern that's slightly too loose matches things it shouldn't.

Function ExtractInvoice(memo As String) As String
    ' Late-bound: no library reference needed.
    Dim re As Object, m As Object
    Set re = CreateObject("VBScript.RegExp")

    ' "inv" or "invoice", then optional space / dash / #, then 3-6 digits.
    re.Pattern = "\binv(?:oice)?[\s#-]*(\d{3,6})\b"
    re.IgnoreCase = True
    re.Global = False

    If re.Test(memo) Then
        Set m = re.Execute(memo)(0)
        ExtractInvoice = "INV-" & m.SubMatches(0)     ' normalised form
    Else
        ExtractInvoice = ""
    End If
End Function

Sub NormaliseMemos()
    Dim r As Long, lastRow As Long
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For r = 2 To lastRow
        ActiveSheet.Cells(r, 2).Value = ExtractInvoice(CStr(ActiveSheet.Cells(r, 1).Value))
    Next r
End Sub

Paste into the Visual Basic Editor (Alt + F11 → Insert → Module), then run and save as macro-enabled (.xlsm).

• Pull an invoice number out of a free-text bank memo, however it was typed.
• Strip everything except digits from a reference field.
• Validate that a code matches the expected shape before importing it.

A simulation — press Run to perform what the macro does to a sample workbook.

• The Like operator — Built into VBA with simpler wildcards — enough when the shape is fixed and you only need a yes/no.
• SUBSTITUTE, FIND and MID — Fine for one or two known formats. A pattern earns its place once the variations multiply.