Limiting * wildcard when using Like patterns

kendocon

New Member
Joined
Jun 14, 2021
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,323
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,141,814
Messages
5,708,732
Members
421,588
Latest member
Wawie

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
Top