How to use Regular Expressions with Excel VBA(Part 2) 

Thank you very much for awesome tutorials Paul

This is my second video on using regular expressions with VBA. I hope you will find it useful. Please add any questions or comments below😀😀.

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.

Can’t wait for the next video. Regex is one of the most powerful objects out there

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!

Thanks for the video. I am looking forward to see part 3 of this series. Thanks again

Looking forwards Part3

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.

Dear Paul, Eagerly awaiting your tutorial on Quantifiers.

Terrific, as always. Thank you, Sir Paul!

Thanks a bunch for sharing the most valuable tricks with us Paul; cannot wait to watch PART-3 though ;)

Am I correct that there is no Part 3, covering Quantifiers within Regular Expressions? Can't seem to locate it if it exists...thanks Paul, you rock.

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.

Great job Sir, very informational, beautifully explained!

Thanks very very much Sir

Perfect, thank you.

Awesoem.... We would love to see this as an article in ur website

This video is awesome. Thank you Paul.

Amazing content! very helpful and your explanation is easy to follow, keep up the great work!

It's getting better and better :)

Great videos. Very clearly explained and excellent examples.

Great tutorial, very helpful. Does part 3 out yet?

Learning so much!! Thank you!! :D

Hi Paul, Looking for one more video on Regular expression, Covering quantifiers.

Thanks you very much if do that all in a Pattern, can I use "^Report.*[A-E]2019.*csv$" ?

12 : 11.How can I get the picture at the end of the video, about "VBA Workbooks, Worksheets and Ranges"? Thanks a million.

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?

hello, the third class of regular expressions < *+?{n} - Quantifiers > has already been published?

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]

Hi Paul, thanks for part 2, is part 3 stills coming?

Dear Paul, the 2 videos on RegExp are wonderful. have you released the 3rd one, with quantifiers? i could not find in your channel.Am I missing it?

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!?!😇

thanks for your explanation :-)

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.

Nice. But I am waiting the Quantifiers video, followed by Regex part 2.

Very usefull but where is part 3??

Just a remark Paul, the dot in .csv$ regexp pattern should be escaped by \ in order to match .csv expression. Nevertheless, your videos are awesome ;)

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

Ah ! I have used to use lots of regulars expressions in Unix, Linux and all but I thought that wasn't possible in Excel VBA. Silly me.

.