Seperare numbers from words

[shahanah]

Hi I'm a beginner in excel. My query is mostly that I need to seperate National Identity Card Numbers from names. The issue is that the numbers are not in the same order for all the different rows. I have 40,000 data to split. Delimited is working but since the National Identity Card Numbers are in different positions, it ends up being in different cells, thus making it difficult to extract all the numbers. Is there any way I could do to extract all National Identity Numbers?

My data is mostly like this:
E.g. (fictitious)
Row 1- Sam Smith S0303964500450
Row 2- W0404761900890 Dr Wong Ten
Row 3- Mr X2112789000870 Xavier
And it goes till row 40000.
Is there any way I could extract all the National Identity Card Numbers from the names? Thanks

[#2]

@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.