Re: Regular Expressions in VBA
Quote from cramirez06;187758Thanks Richie, that site really helped. I have another question though: I have only just started learning regex so it might be a stupid one, but... Do you know how I could turn a string into a regular expression pattern? More exactly, if I have a string
"6-methyl-5-hepten-2-one", how can I turn it into a regex withing VBA code to search just for "5-hepten-2-one" or something similar? Because I will have to do so for many such names.
You're probably looking for something like:
"^[0-9]-methyl-[0-9]-hepten-[0-9]-one$" to match your string but with any single number instead of 6, 5, and 2 specifically like 8-methyl-2-hepten-1-one. You can also use ^ and $ to match the beginning and end of the line. The "-" has to be included between each element.
"[0-9]+-methyl-[0-9]+-hepten-[0-9]+-one" is the same as above but with one or more numbers instead of one like 889-methyl-4-hepten-1235347686572465235758573467-one
"[0-9]+-[A-Za-z]+-[0-9]+-[A-Za-z]+-[0-9]+-[a-z]+" is the same as above but the words can be any combination of upper and lower case letters, except the last is only lower case like 2235-WhatEVEr-98-mOrEWORdswithOUTSPaces-098-onlylowercasehere
"[0-9]*[02468]-[A-Za-z]+-[0-9]{3,5}-[A-Za-z]+-[0-9]{3,}-(one|two|five|nine)" matches even numbers ([0-9]* matches 0 or more but must end with even number), a word, 3-5 numbers, another word, at least 3 numbers, and then one of the words one, two, five, or nine. () groups things together and | means 'or'.
"([0-9]+-[A-Za-z]{3,10}-?)+" matches any number, then any word 3-10 characters long, but then repeats that as many times as it can. It matches 8-hepten and even 945-hepten-9867-oxy-8976-one-987-eight-987-morewords-9842-methyl... The "?" matches 0 or 1, so the "-" won't be at the end of the last matched word. If you know it will always be a group of 6 (3 number-word combos), then you can replace the + with {3}.
These might not be exact, but they should give an idea of how specific or general they can define your strings.
Regular expressions make matches more precise and much more efficient to program than parsing text and using a couple wildcards. I didn't learn much about them until I did analysis outside of Excel, but I wish I had learned them earlier. This is a small contribution to introduce it to others that wouldn't come across it otherwise because it should be a more prominent tool in VBA. I start with a few strings I want to match, and some I don't want to match, and replace each part with a more general match, similar to shown above. Test it and make sure it works like expected.