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!

 

[Oscar Cronquist]

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Laura Hill ABC123[/td][td]

TRUE​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]John Smith ABC123[/td][td]

TRUE​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Jennifer Martin[/td][td]

FALSE​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Kate Doe GUY432[/td][td]

TRUE​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]John Doe GAL0987[/td][td]

TRUE​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet21[/td][/tr][/table]

User defined function in cell B1:

=Compare(A1)

Function Compare(c As Range) As Boolean
Dim pttrn As String
pttrn = "*???###*"
Compare = c Like pttrn
End Function

 

[cheoksoon]

Thank you very much!! Problem solved!

 

[Peter_SSs]

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

If that, and the samples in post #2 , are what you have, and you are only using vba to create a function to use on the worksheet, wouldn't this standard worksheet function do the job?

Excel Workbook
	A	B
1	Laura Hill ABC123	TRUE
2	John Smith ABC123	TRUE
3	Jennifer Martin	FALSE
4	Kate Doe GUY432	TRUE
5	John Doe GAL0987	TRUE
Check if ID

 

[cheoksoon]

Hi Peter,

I thought about that too, but the data I have is very haphazard...

It could be "John Smith ABC1234", "GAL4321 Sarah Jones", "Michael Douglas (GHY7656)", "HUJ9899 / Kim Matterson".

So I really need a formula to look for a pattern.

Is there a spreadsheet function which could do that?

 

[Peter_SSs]

It could be "John Smith ABC1234", "GAL4321 Sarah Jones", "Michael Douglas (GHY7656)", "HUJ9899 / Kim Matterson".

Is there a spreadsheet function which could do that?

How would this one go?

Excel Workbook
	A	B
1	Laura Hill ABC123	TRUE
2	John Smith ABC123	TRUE
3	Jennifer Martin	FALSE
4	Kate Doe GUY432	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
Check if ID (2)

 

[cheoksoon]

Hi Peter,

I never thought of that, but that will work too!

Just out of curiosity, this won't happen (I hope) with my list of clients, but there might be duplicate names:

1. John Doe ABC1234
2. Jane Doe GAL4321
3. John Doe(2)
4. GUY6789Richard Smith

Can the formula differentiate between (3) and the rest?

 

[Peter_SSs]

Can the formula differentiate between (3) and the rest?

That may be possible but it might be a case that a user-defined function like Oscar's may be simpler.
However, if you want to investigate whether worksheet functions can achieve your goal, instead of a series of minor changes to creep up on it, let's see if we can have a clear set of rules. Answers to these would help clarify the problem.

1. All your sample ID's are 3 letters followed by 4 digits. Are all IDs that pattern?

2. Will any 'duplicate' number always be in parentheses like your one example?

3. Will any 'duplicate' number always be at the end like your one example?

4. Would it be possible to have tens, hundreds or even thousands of duplicates? eg John Doe(897)

 

[cheoksoon]

That may be possible but it might be a case that a user-defined function like Oscar's may be simpler.
However, if you want to investigate whether worksheet functions can achieve your goal, instead of a series of minor changes to creep up on it, let's see if we can have a clear set of rules. Answers to these would help clarify the problem.

1. All your sample ID's are 3 letters followed by 4 digits. Are all IDs that pattern?

2. Will any 'duplicate' number always be in parentheses like your one example?

3. Will any 'duplicate' number always be at the end like your one example?

4. Would it be possible to have tens, hundreds or even thousands of duplicates? eg John Doe(897)

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

2. There won't be a duplicate ID because they're meant to be unique. But I get the duplicate names sometimes and those come with a number in parenthesis. (i.e. John Doe (2))

3. Yeap, it is always at the end

4. Oh no, that's my job to ensure everybody gets a unique ID so to remove 'duplicates' and also to assign a unique ID to those names which came without one. In other words, I'm doing a very poor job if I let the duplicates get up to 897. Lol. I just get a random set of data from another person which is kinda haphazard.

 

[Peter_SSs]

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?