-1

I am looping through an Array of Image Filenames which are as below:

  1. ConImage1 - Core.png
  2. ConImage211 - Core.png
  3. ConImage34 - Core.png
  4. ConImage09 - Core.png
  5. ConImage11 - Core.png
  6. ConImageOri23.png
  7. ConImageOri2.png
  8. ConImageOri11.png
  9. ConImageOri132.png
  10. ConImageForEng7 - Core.png
  11. ConImageForEng12 - Core.png
  12. ConImageForEng11 - Core.png
  13. ConImageForEng10 - Core.png
  14. ConImage1-Dislikes-Core.png
  15. ConImage1-Likes-Core.png
  16. ConImage12 - Dislikes - Core.png
  17. ConImage12 - Likes - Core.png
  18. ConImage34 - Dislikes - Core.png
  19. ConImage34 - Likes - Core.png
  20. ConImage55 - Dislikes - Core.png
  21. ConImage55 - Likes - Core.png

I need a Regex pattern that will only match the following Filenames:

  1. ConImage1 - Core.png
  2. ConImage211 - Core.png
  3. ConImage34 - Core.png
  4. ConImage09 - Core.png

  5. ConImage11 - Core.png

  6. ConImageOri23.png

  7. ConImageOri2.png
  8. ConImageOri11.png
  9. ConImageOri132.png

and exclude the filenames containing the words : Likes, Dislikes, ForEng etc.

Edit:

I am not good at regex but trying something like this to limit my search.

^(ConImage|(Orig|!ForEng)){1}[0-9\ \-]+[\W(Dislikes|Likes)][0-9\ \-]+(Core\.png)$/i/g

OR the below works for finding necessary files, but if some odd name comes out e.g. ConImageMaxx12 - Core.png etc, it also picks that up.

^ConImage((?!ForEng|Dislikes|Likes).)*$

I don't want to implement VBA.Filter function but need a Regex solution.

Any help would be most appreciated.

braX
  • 11,506
  • 5
  • 20
  • 33
sifar
  • 1,086
  • 1
  • 17
  • 43
  • 1
    Why not simply use `InStr`, as shown [here](https://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string/15585089)? – npinti Jan 20 '20 at 13:42
  • 1
    Remove `/i/g`, VBA regex does not support regex literal notation. There are special options, `RegEx.Global = True`, `RegEx.IgnoreCase = True` etc. – Wiktor Stribiżew Jan 20 '20 at 13:45
  • @npinti i am using regex in my code. – sifar Jan 20 '20 at 14:31
  • @WiktorStribiżew yes i know. just put it there to indicate i need case-insensitive matches. – sifar Jan 20 '20 at 14:32
  • You could always just use `Filter` function on your array instead, excluding those that have any of those words in their string. – JvdV Jan 20 '20 at 14:49
  • So is there no regular expression pattern to find the given filenames without resorting to VBA functions? From the comments, can i assume that it is not possible? – sifar Jan 20 '20 at 14:54
  • *there no regular expression pattern to find the given filenames without resorting to VBA functions* - true, if you do not want to use VBA functions, you do not need any regex, since you won't be able to use it. – Wiktor Stribiżew Jan 20 '20 at 14:57
  • I was thinking of something like this to limit my search.`^ConImage((?!ForEng|Dislikes|Likes).)*(.png){1}$`. This works well for finding necessary files, but if some odd name comes out e.g. `ConImageMaxx12 - Core.png` etc, it also picks that up. – sifar Jan 20 '20 at 15:23
  • Might be useful to update your question with the actual code you're trying (so that anyone who wants to help out doesn't have to write/find it themselves), along with the actual regex pattern: the one you posted is not the one you tried, so that may have put some folk off.... – Tim Williams Jan 20 '20 at 16:05
  • How do you expect to use Regex in Excel without using VBA? – Ron Rosenfeld Jan 21 '20 at 00:42
  • What are your specific rules? `exclude Likes, Dislikes, ForEng etc ` is not really specific enough for a computer due to the **etc**. – Ron Rosenfeld Jan 21 '20 at 01:59

1 Answers1

1

Without specific rules, it is hard to come up with a regex. To match the specific values you have posted (and exclude the ConImageMaxx12 you mentioned in a comment), you could try:

Edit to correct the missing "Start of String" token

^ConImage(?:Ori)?\d+(?:(?!(?:For|Likes)).)*$

That regex matches:

  • ConImage
  • optionally matches Ori
  • matches one or more digits
  • and then matches the rest of the string so long as it does not contain the substrings in the negative lookahead (which would include ForEng, Likes, Dislikes and other derivatives)

Whether that set of rules is sufficient to eliminate the unwanted file names cannot be stated, since I am only guessing at some rules based on what you've written.

Here is a more formal explanation of the Regex with some links to a tutorial explanation:

Note that, in accordance with your comments, we set the case-insensitive option

Match Specific File Names

^ConImage(?:Ori)?\d+(?:(?!(?:For|Likes)).)*$

Options: Case insensitive; ^$ match at line breaks

Created with RegexBuddy

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thanks Ron. Since it works with the above 5 types of filenames found in a folder, I only changed the above pattern slightly `^ConImage(?:Ori)?\d+(?:(?!(?:ForEng|Likes|Dislikes)).)*$`. Is this correct? I am getting full matches. – sifar Jan 21 '20 at 06:44
  • @sifar Based on the information you have provided, **No**. The regex I provided worked on the information you provided, and making it more complex was not indicated. Was there some problem with the original, so far as matching was concerned? Was it filtering out files that should have been matched? – Ron Rosenfeld Jan 21 '20 at 11:01
  • For example you have used `For` instead of `ForEng` and `Dislikes` and `^` were missing, so got confused. – sifar Jan 21 '20 at 11:04
  • @sifar The `^` missing is a typo and should have been included (I will edit my answer).. But your other changes unnecessarily add a comparison operation. – Ron Rosenfeld Jan 21 '20 at 11:17
  • Understood now. Appreciate your guidance. – sifar Jan 21 '20 at 11:19