Hi all,
Which pattern would I have to use to get all unique fiscal years in the following string:
Currently I use
Please Login or Register to view this content.
The desired result should bePlease Login or Register to view this content.
Please Login or Register to view this content.
You have already done the heavy lifting. (The parentheses around "FY.." are not necessary, but they don't hurt either.) To view your results:
This will print the desired results to the Immediate window as you have shown in your post. Where do you want your desired results to actually appear?Please Login or Register to view this content.
You can try this;
Please Login or Register to view this content.
Last edited by Haluk; 06-26-2020 at 12:41 PM.
Do you really need to use Regular Expressions for this? The following code creates a Matches array (zero-based) that contains the unique FY's. The output for it shows in the Immediate Window.
NOTE: I noted you used Str as a variable name (I changed it to S in the above code). Str is the name of a built-in text function and, while VBA will let you do it, it is not a good idea to use function names for your variable names.Please Login or Register to view this content.
Last edited by Rick Rothstein; 06-26-2020 at 01:38 PM.
One more option without RegEx since you have office 365 using "Unique" function
Please Login or Register to view this content.
If I was able to help, you can thank me by clicking the * Add Reputation under my user name
Yes, that probably will work for the OP as he has the same version of Excel as you do; however, it does not work for me with my version of Excel.
@Rick Yes, it wouldn't work on older versions that's why I specifically stated office 365 for the OP... Perhaps it's time to upgrade & explore the new options
By the way, congrats on crossing the 1200 posts & getting Forum Expert title which you definitely deserve
Thank you for the congrats and kind words... much appreciated. To tell you the truth, though, I am kind of surprise you noticed that.Originally Posted by nankw83
By the way, congrats on crossing the 1200 posts & getting Forum Expert title which you definitely deserve
RegEx with unique matchesPlease Login or Register to view this content.
Another alternative to get a unique list, is using the "Dictionary" object. (In this case, we don't have to deal with On Error... statement)
Please Login or Register to view this content.
Last edited by Haluk; 06-27-2020 at 05:00 AM.
Thanks guys!! All very helpful and getting the results I need. However, I was wondering if there is a way to get the same output by using a "negative lookahead" inside the RegEx pattern instead? Is that feasible? I'm new to RegEx hence trying to understand how powerful it is.
No in vba regexp.
Good to know. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks