Forum Discussion

shahanah's avatar
shahanah
Copper Contributor
Dec 21, 2021

Seperare numbers from words

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
excel

1 Reply

  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Silver Contributor
    Dec 21, 2021

    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.

     

     

     

Resources