Group string based on comma using regex

[YasserKhalil]

Hello everyone

Say I have string separeted by comma like
Yasser, Ahmed, Reda, Khalil
How can I group each part of those words within string using regex pattern?
I am searching for something similar to (\,.*)(.*\,) but this is not right

so I can isolate the strings using regex like that
Yasser
Ahmed
Reda
Khalil

Thanks advanced for help

[CK76]

In your sample case "(\w+)" will do.

Edit: Forgot to remove ?

[Marc L]

Hi,

why don't you use the easy Split VBA function ?

[YasserKhalil]

Thanks a lot for replies.
That worked fine (\w+). Thanks a lot for solution.

@Marc L
I am just training some patterns on regex and I know Split is the best in this case.

[YasserKhalil]

Say A1 has the string Yasser, Ahmed, Reda, Khalil, Ahmed, Khalil
How can using regex color the duplicate values within such pattern?

[CK76]

You can use some container to hold matches from pattern and then check for duplicate (dictionary, collection, two arrays etc).

Ex: Using dictionary, add matches as Key. If .exists(string) in dictionary increment item by 1. Then use Key to find match in string and use position of string to format it.

[YasserKhalil]

I already tried but didn't succeed

Please Login or Register  to view this content.

Try the code with
A1 = 2,4,6,8,12,14,18,23,35,78,101,38,30,205,2,101
and you will see what I mean

[CK76]

See below sample.

Please Login or Register  to view this content.

[YasserKhalil]

That's great. Thanks a lot
As a matter of curiosity, is it possible to skip the first instances and highlight the rest?
I mean like that
01.png

[CK76]

Sure, that was the original intention when I used Redim y(1 to dic(Key))

Modify 2nd loop to something like...

Please Login or Register  to view this content.

Edit: Woops, should have removed n in the n = calc. Code updated.

[YasserKhalil]

Thanks a lot for awesome help
Best and Kind Regards

** I wish to be able to use regex for this purpose too(that will be open for other members) but for now this is solved.

[CK76]

You are welcome and thanks for the rep

As for Regex, you only need to replace the logic to fill dictionary using Set matches = regex.Execute(string). Then looping match/submatch in the object.

You'll probably find below link useful.
https://analystcave.com/excel-regex-tutorial/

[jindon]

Group string based on comma using regex

Please Login or Register  to view this content.

[YasserKhalil]

Thanks a lot jindon for this masterpiece. I will try to study and get it well so as to learn new and we are learning the new from you all the time.

[YasserKhalil]

Can you please explain the pattern " *([^,]+)" and why this pattern (\w+) doesn't work with your code?

[jindon]

Huhhh?

Comma delimited string, so get the string other than comma.
\w is not appropriate. This will only extract A-Za-z,space & 0-9, so if string contains hyphen/period/question mark, etc., it will not separate correctly.

Why \w+ doesn't work? Because the pattern contains 2 parts.
try change to " *(\w+)"

[YasserKhalil]

Thanks a lot .. so the secret in using space and asterisk before the pattern. Is it necessary in this case for any pattern that may be used?

[jindon]

I don't know, but I think that's better, because who knows your actual string?
It might contain comma and with/without space(s), so it will trim the spaces in

Please Login or Register  to view this content.

So that it will target to the trimmed word only.

[YasserKhalil]

Last point, I tried to skip the first instance in your code by editing this

Please Login or Register  to view this content.

But I am still get all the instances higlighted.

[YasserKhalil]

I could figure this point out but it is matter for me to give me your opinion

Please Login or Register  to view this content.

[jindon]

Code is not designed such, so you should try to build from a scratch for yourself.

I will tell you that it will be not so simple...

[jindon]

Please Login or Register  to view this content.

[YasserKhalil]

That's better certainly.
Thank you very much for your pateince

[YasserKhalil]

Sorry for disturbing you again
I have tested the code now with this string

Please Login or Register  to view this content.

Notice 2 is repeated three times so I intend to skip the first instance and highlight the others
When running the code, I encountered an error "Invalid procedure call or argument" at this line

Please Login or Register  to view this content.

[jindon]

Please Login or Register  to view this content.

[YasserKhalil]

Thank you very very much. That's awesome
Best and Kind Regards