Searching for patterns of text with VBA

[cheoksoon]

Hi there,

How do I search for a pattern of text in cell using VBA?

I've looked into the instr and find method but this still eludes me.

For example, I've got a list of clients with special IDs attached next to their names. (i.e. John Smith ABC1234).

How do I search the cells to determine if the special ID is present (i.e. ABC1234, GUY4321, GAL0987, etc.) using VBA?

Thanks!

 

[cheoksoon]

There seems to be a little confusion. When talking about duplicates, I was never referring to IDs, I was referring to names only.
Could you re-visit my questions 2, 3 and 4 and answer only in relation to duplicate names?

Sorry,

2. Yes, the duplicate number is always in parenthesis

3. Yes, they are always at the end

4. No, it might go up to 2 or 3, but not 897.

 

[Peter_SSs]

1. Yeap, all my unique IDs are 3 letters followed by 4 digits.

2. Yes, the duplicate number is always in parenthesis

3. Yes, they are always at the end

4. No, it might go up to 2 or 3, but not 897.

In that case, if the duplicate name number would always be less than 10, see if this does what you want.

Excel Workbook
	A	B
1	Laura Hill ABC1923	TRUE
2	John Smith ABC1283	TRUE
3	Jennifer Martin	FALSE
4	Kate Doe GUY4372	TRUE
5	John Doe GAL0987	TRUE
6	GAL4321 Sarah Jones	TRUE
7	Ann Louise Smith	FALSE
8	Michael Douglas (GHY7656)	TRUE
9	HUJ9899 / Kim Matterson	TRUE
10	John Doe(2)	FALSE
11	GUY6789Richard Smith	TRUE
12	John Doe GAL0987(8)	TRUE
Check if ID (2)

 

[cheoksoon]

In that case, if the duplicate name number would always be less than 10, see if this does what you want.

Check if ID (2)

	A	B
Laura Hill ABC1923
John Smith ABC1283
Jennifer Martin
Kate Doe GUY4372
John Doe GAL0987
GAL4321 Sarah Jones
Ann Louise Smith
Michael Douglas (GHY7656)
HUJ9899 / Kim Matterson
John Doe(2)
GUY6789Richard Smith
John Doe GAL0987(8)

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:199px;"><col style="width:108px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]FALSE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]TRUE[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 =ISNUMBER(AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},LEFT(A1,LEN(A1)-3)),1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Yeap, that will definitely work!

Thanks for all the help.

 

[Peter_SSs]

Yeap, that will definitely work!

Thanks for all the help.

You are very welcome.