Seperare numbers from words

shahanah 

You may try the following User Defined Function (UDF) which can be used as a Regular Excel Function on the Worksheet.

Function getCardNumber(ByVal str As String) As String
Dim Matches As Object
With CreateObject("VBScript.RegExp")
    .Global = False
    .Pattern = "[A-Z]\d+"
    If .Test(str) Then
        Set Matches = .Execute(str)
        getCardNumber = Matches(0)
    Else
        getCardNumber = ""
    End If
End With
End Function

Then assuming your string is in cell A2, try =getCardNumber(A2) in B2 and copy it down.

Please find the Macro-Enabled Excel Workbook with UDF placed on Module1. Press Alt+F11 to view the Code.