Searching strings for patterns.

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
I have a userform called 'frmProcessReturns'.
On the form is a textbox named 'txtServicesReceived' and a listbox named 'lstServiceType'. The listbox has a single column and has the MultiSelect option set to fmMultiSelectMulti. Each value in the listbox is taken from an Access table called 'tblServiceType'.

The textbox is populated from a cell in a different workbook which is filled in by various little old ladies around the county, and being little old ladies they don't make it easy for the computer to figure out which services are being received - rather than put in something simple like 'MOW', they'll put in 'Meals-On-Wheels' (or sometimes 'Meals On Wheels' or maybe 'M.O.W').... where I just need 'MOW'. They could also add to this box the fact that they get 'Resi Care - Initial', where I just need to know that they get 'Residential Care'. Or in extreme cases they might put '2:30pm on Fridays' or 'I don't like crayons' (these are council workers after all) :LOL:

Anyway, what I'm after is some kind of procedure (or just to be pointed in the right direction) on how to search the textbox (or the underlying cell) for each occurence of a word that matches these patterns.

Also, if it's not too much to ask, I'd like to be able to rank the words. For example if the answer is NFA (no further action) and RIP then I'd like it to rank NFA above RIP, but if the answer is MOW and RIP then MOW ranks higher than RIP.

The idea is that the listbox then highlights the best guess at services received.

Would this be easy to do in VBA? Or, seeing as there's already a connection open to a database could I use some kind of Access query for pattern matching?
I'm using Excel 2002 & Access 2000.

Please tell me if that makes sense! And any help would be greatly appreciated as usual.
 

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

rjp

Board Regular
Joined
Jan 5, 2006
Messages
148
There's never going to be a perfect solution to this unless you restrict the user entries to a predefined list in a list box or something.

If this is too inflexible then you may get some joy with regular expressions. They're a very powerful method of analysing patterns in strings and once you've got over the initial learning curve they're very easy to work with. You may even find them useful in other tasks.

You'll need to add a reference to the regular expression library (Tools > References in the VB Editor). On my machine the library is called "Microsoft VBScript Regular Expressions 5.5".

Also check out this page on MSDN that details their use:
MSDN Regular Expression API Reference
 

Darren Bartrup

Well-known Member
Joined
Mar 13, 2006
Messages
1,296
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply rjp.

I am hoping to restrict the user entries eventually and turn it into an online collection form of some sort (but there's issues with security as the form includes personal information), but for now I have to work with what I've got and what the little old ladies are used to.

I'll go check out the regular expressions - never heard of them before :)
 

Forum statistics

Threads
1,137,366
Messages
5,681,068
Members
419,950
Latest member
BeckiJae

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