VBA RegEx for Negation

I am facing an issue with regular expressions.

I am trying to get numbers which aren't followed by centimeters or inches as shown in below examples:

1. 30.5 XXL Height 175.5 cm - this should return only 30.5 as 175.5 has "cm" attached to it

2. 34 inches XL 54 - this should return only 54 as 34 has inches

How can I do this with a regular expression?

Use negative lookahead (?!).

Regex: \d+(?:\.\d+)?(?=(?: (?!cm|inches)|$))

Details:

• (?=) Positive lookahead
• (?:) Non-capturing group
• (?!) Negative lookahead
• | Or
• $ Asserts position at the end of a line

VBA code:

Set re = CreateObject("VBScript.RegExp")
re.Global = True

re.Pattern = "\d+(?:\.\d+)?(?=(?: (?!cm|inches)|$))"
For Each Match In re.Execute("30.5 XXL Height 175.5 cm")
    Debug.Print (Match)
Next

Output:

30.5