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
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
.Global = False
.Pattern = "[A-Z]\d+"
If .Test(str) Then
Set Matches = .Execute(str)
getCardNumber = Matches(0)
getCardNumber = ""
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.