Limiting * wildcard when using Like patterns

[kendocon]

I'm abstracting data from large text cells, sometimes up to 600 characters long. Within the text I am searching for patterns matching like T#, N#, M#, saving this into a string, and the recording the numbers for t n m in their own columns.

Frequently these patterns are typed differently though in the data, for example: "T1, N1b, M1" or "pT1, N0, M1a" or "T0 pN1 pM0" or "T1,N1,pM0" etc.
Sometimes they have spaces or no spaces between commas, sometimes they have commas or only spaces, and sometimes they have a letter before or after. (casing of t n m can also change)

Currently, I have tons of patterns written out using the like operator, with text compare default:
If....Like "*T#, N#, M#*"
Or Like "*?T#, ?N#, ?M#*"
Or Like "*T#?, N#, ?M#*"
Or Like "*?T#, ?N#?, ?M#*"
etc etc...
It works for the most part, but I don't know that I've covered all possible patterns, and it's quite messy looking.

I'm wondering if there is a way to place a limit on *. Instead of zero to any amount of characters, I'd like to have zero to any 3 or 4 characters.
Doing this I could just use "T#*N#*M#" and not worry about it abstracting out a substring that is 200 characters long because t n and m were found in other words.

I have tried to do "*T#*N#*M#*", which extracted substrings that are including extra words and sentences.
Output for example was "AT1 and the crew is incorrect with n200 because sbm7 was included"

I'm not a "brand new" vba user, but I am still fairly novice.
Thanks for any help

 

[Akuini]

Hi, @kendocon. Welcome to the Forum

Sometimes they have spaces or no spaces between commas, sometimes they have commas or only spaces,

Assuming you don't need the comma in your result then you can put the text into a variable then replace all comma to space, so you can use more efficient pattern .

I'm wondering if there is a way to place a limit on *. Instead of zero to any amount of characters, I'd like to have zero to any 3 or 4 characters.

After you find the part that match the pattern you can check the length, say if it is below 20 character, something like:
If Len("the words") < 20 then ...
so "AT1 and the crew is incorrect with n200 because sbm7 was included" will fail the test.

Note:
Another option is to use Regular Expression.