Find ID number

saksaganskis

New Member
Joined
Aug 7, 2019
Messages
4
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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
post representative example (in table form) of your source data and expected result
 
Upvote 0
post representative example (in table form) of your source data and expected result

I have made few rows with results:
DescriptionResult
GB24BKEN10000031510604 NAME SURNAME /ID/ NAME SURNAME Details: ID12345TRUE
GB24BKEN10000031510604 NAME SURNAME /ID/ Details: ID 54321 NAME SURNAMETRUE
GB24BKEN10000031510604 NAME SURNAME /ID/ Details:ID4321TRUE
Fee for Funds TransferFALSE
Beneficiary: NAME SURNAME /ID/ Reg No 123456789 Details: Agreement Nr2019-1FALSE
Received money from NAME SURNAME with reference ID123456TRUE
ID654321TRUE
ID 9854 supportTRUE

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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"

Code:
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
ABCD
1DescriptionResult
2GB24BKEN10000031510604 NAME SURNAME /ID/ NAME SURNAME Details: ID12345TRUETRUETRUE
3GB24BKEN10000031510604 NAME SURNAME /ID/ Details: ID 54321 NAME SURNAMETRUETRUETRUE
4GB24BKEN10000031510604 NAME SURNAME /ID/ Details:ID4321TRUETRUEFALSE
5Fee for Funds TransferFALSEFALSEFALSE
6Beneficiary: NAME SURNAME /ID/ Reg No 123456789 Details: Agreement Nr2019-1FALSEFALSEFALSE
7Received money from NAME SURNAME with reference ID123456TRUETRUETRUE
8ID654321TRUETRUETRUE
9ID 9854 supportTRUETRUEFALSE
10abc ID1E1TRUEFALSEFALSE
Find ID
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top