This series on RegExp has been a game-changer for my little Excel-based bank-account tracking and budget app. And to think I only discovered this now. I still need to get a better grasp of the finer points of using Regular Expressions, but these videos - as well as the one on late vs early binding... what a goldmine there! - has truly transformed managing incoming data to the worksheets. I'm retired (from nursing) and so on a fixed income, but even so, am going to save up for your course (I live in Australia, so everything is more expensive here). There are apps to be written and VBA actually offers really cool solutions: many people already have some version of MS Office, so nothing to install and with late binding, nothing to activate in the VBE.
You're such a tease! Quantifiers! that was going to be my first question - can we combine patterns into one statement....now I gotta wait...lol! thanks again, great stuff!
Nice, clear explanations, Sir Paul. Thanks. ✔ 2:27. I take it that "regEx.Global = False" works like the "distinct" keyword in a SQL SELECT clause. 10:47. "Square parentheses" - called "square brackets" over here in the U.S.A. Two great peoples divided by a shared language. We are still waiting for "Quantifiers" to appear. Thanks again.
It is awesome! I am grateful for your effort. I have few more topics for you to explain: lookaround, grouping, replacing of group. I hope to see it soon. Greetings.
Great video, Paul. This is better than aspirin! I’ve been needlessly banging my head against the wall with instr, mid, left, etc. for too long. I can tell by the comments part 3 will be a big hit when it comes out.
Much useful.We are waiting for 3rd part where quantifies will cover. Also, I have one query if we need to remove all special characters then what will be our pattern.
You're wrong about the dot (period). It matches all characters except the newline. If you want to match a dot, then you need to escape it. To escape the dot, just precede it with a backslash, such as (without quotes) "\." In your example of matching csv file extensions, your regular expression will match (without quotes) "9csv", "ucsv", or even " csv" (space followed by csv) among others. So, if you want to match csv file extensions, your regular expression should be (without quotes): "\.csv$".
I've always struggled with those. Am looking for a pattern of open square bracket (any letter or word or character or number) then close square bracket, like [Example] or [this 1]. What would the pattern be?
Is there a way to use it to sort a list of codes consisting of [A-Z]+, \d+ , [A-Z] for example FE2A or SCD21B alphabetically for the first [A-z]+, and for those with the same [A-Z]+ to be sorted from lowest to highest number, then for codes that have the same [A-Z]+ and \d+ to be sorted alphabetically by the last [A-Z]
Hey Paul!✌️ Hope you're doing fine! Thanks for your work! I'm searching for the 3rd part of RegEx in VBA, where can I find it or doesn't it even exist?😅 If it's not existing yet, might be it's a good theme for a next video!?!😇
Hello, i really like your videos - At the end of this video you say the next video will cover 'qualifiers' for regular expressions. What is the title of the video that you use 'qualifiers' for regular expressions?
Hello, I created an excel sheet identical to yours at 9:00 and input the code exactly as you had, but am getting a Run-time error '424' Object required error. Any idea what I might be doing wrong? It appears from testing that the issue arises from using shFilenames.
2022; this doesn't work, but probably my error. Comes back "Compile error: Variable not defined" - referring to shFilenames.Range("B1:B7").ClearContents Sub RE_Example() 'Clear any existing results shFilenames.Range("B1:B7").ClearContents ' Get the range of filenames Dim rg As Range Set rg = shFilenames.Range("A1:A7") ' create the regular expression Dim i As Long, regex As New RegExp regex.Global = True regex.Pattern = "" ' Read through the filenames For i = 1 To rg.Rows.Count ' Use Test to see if the pattern exists rg.Cells(i, 2).Value regex.Test(rg.Cells(i, 1).Value) Next i End Sub