Find ID number

[saksaganskis]

Hi Guys,

Could anyone suggest me how to find cells containing "ID 1234" or "ID12345" or " ID 123456"? Number could be 4 to 6 digits number.
I need to identify from bank statements those transactions, where ID number is given in the description field. There could be the case when description is something like "paid", containing "ID" letters, but I need to avoid them.

I have found how to return only 4-6 digit number from the text using VBA, but I need a bit deeper search.

Thanks!

 

[sandy666]

post representative example (in table form) of your source data and expected result

 

[saksaganskis]

post representative example (in table form) of your source data and expected result

I have made few rows with results:
[TABLE="width: 1059"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 1059"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Description[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]GB24BKEN10000031510604 NAME SURNAME /ID/ NAME SURNAME Details: ID12345[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]GB24BKEN10000031510604 NAME SURNAME /ID/ Details: ID 54321 NAME SURNAME[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]GB24BKEN10000031510604 NAME SURNAME /ID/ Details:ID4321[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]Fee for Funds Transfer[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]Beneficiary: NAME SURNAME /ID/ Reg No 123456789 Details: Agreement Nr2019-1[/TD]
[TD="align: center"]FALSE[/TD]
[/TR]
[TR]
[TD]Received money from NAME SURNAME with reference ID123456[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]ID654321[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]ID 9854 support[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

If you are looking for a standard formula solution then you could try the formula in column B. Note, however, that it is not entirely robust as it returns True in row 10 which I don't think satisfies your requirement.

An alternative is to use the following user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note that the default for the function is to look for at least 4 digits after "ID". However, if you want to change that (minimum) number of digits then you can do so by adding the second argument into the function as shown in column D where the function is looking for at least 5 digits after "ID"

Function FindID(s As String, Optional MinDigits As Long = 4) As Boolean
  Static RX As Object
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "ID ?\d{" & MinDigits & "}"
  FindID = RX.Test(s)
End Function

Excel Workbook
	A	B	C	D
1	Description	Result
2	GB24BKEN10000031510604 NAME SURNAME /ID/ NAME SURNAME Details: ID12345	TRUE	TRUE	TRUE
3	GB24BKEN10000031510604 NAME SURNAME /ID/ Details: ID 54321 NAME SURNAME	TRUE	TRUE	TRUE
4	GB24BKEN10000031510604 NAME SURNAME /ID/ Details:ID4321	TRUE	TRUE	FALSE
5	Fee for Funds Transfer	FALSE	FALSE	FALSE
6	Beneficiary: NAME SURNAME /ID/ Reg No 123456789 Details: Agreement Nr2019-1	FALSE	FALSE	FALSE
7	Received money from NAME SURNAME with reference ID123456	TRUE	TRUE	TRUE
8	ID654321	TRUE	TRUE	TRUE
9	ID 9854 support	TRUE	TRUE	FALSE
10	abc ID1E1	TRUE	FALSE	FALSE
Find ID

 

[saksaganskis]

Thank you very much!

That works perfect!

 

[Peter_SSs]

Thank you very much!

That works perfect!

You're welcome.

As a matter of interest, did you go with the standard functions or the UDF?

 

[saksaganskis]

I will use a master-file for identification, so UDF perfectly suits.

 

[Peter_SSs]

I will use a master-file for identification, so UDF perfectly suits.

OK, thanks for the clarification.