Excel VBA regex pattern giving error '5018'

Question

Im trying to extract size from product details by using the below REGEX..

With rgx
    .Global = True
    .MultiLine = False
    .Pattern = "([0-9.,*?/]+{1,5}\s*(g|G|ML|ml|mL|Ml|oz|OZ|Oz|ea|\s+)){1,3}"
    
    Set temp = .Execute(Rng)

However, while executing the regex im facing 5018 error in line Set temp = .Execute(Rng) Why im getting this error anything wrong in h eregex?

Rng is the cell value example values can be Size:1.70 oz, size:100ml, size 10.5g or size:100 ml + 50 ml — Linga
– Linga, Commented Apr 1, 2021 at 7:53
But +{1,5} is wrong syntax too. The + is not quantifiable in this case. You'd probably just want to remove the "+" alltogether. Then again, if you explain a bit more what you are after, there may be a better pattern here. — JvdV
– JvdV, Commented Apr 1, 2021 at 7:54

Ryszard Czech · Accepted Answer · 2021-04-01 21:40:14Z

0

As noted in this post, 5018 stands for "Unexpected quantifier in regular expression".

Look at [0-9.,*?/]+{1,5} where there is +{1,5}, two quantifiers at a row.

In VBA regex this is an error.

If you match 1 to 5 characters belonging to the [0-9.,*?/] set, use [0-9.,*?/]{1,5}.

If you match one or more, use [0-9.,*?/]+.

Do not chain quantifiers.

answered Apr 1, 2021 at 21:40

Ryszard Czech

18.7k4 gold badges27 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Linga Over a year ago

Thank you for the detailed explanation. One clarification if i want to capture Size where 100 ml + 20 ml what could be the pattern (regex) for it?

Ryszard Czech Over a year ago

@Linga Your expression looks good, note how to extract all matches at stackoverflow.com/questions/44979363/…. Expression like (\d+(?:[,.]\d+)*)\s*(g|G|ML|ml|mL|Ml|oz|OZ|Oz|ea) could work for you.

Collectives™ on Stack Overflow

Excel VBA regex pattern giving error '5018'

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related