Excel VBA Split on regexp

Asked Jun 27 '17 at 20:57

Active Jun 27 '17 at 20:57

Viewed 57 times

I am writing a Excel VBA macro and need to split a string on a number. What I had hoped would work is this:

Split("ABC2_DEF",[0-9]*)(0)

Which would give me

ABC

Every combination I tried either interprets literally or is invalid. I cannot seem to find how to use a regex in the Split function and perhaps it is not possible. I was hoping to avoid a brute force method. Thanks.

EDIT: I found the function RegexExtract that worked great. Would still like to know if I can natively Split on regex.

asked Jun 27 '17 at 20:57

fastzombies

To be clear, you can use Regex to do what you want with find/replace...but you're looking specifically for using it with `SPLIT()`? – BruceWayne Jun 27 '17 at 21:04
VBA regex has no `Split` method. You can only `Execute`, i.e. *match* (or replace with `Substitute`, or check for a match with `Test`). – Wiktor Stribiżew Jun 27 '17 at 21:06
Ok, thanks. The `RegexExtract` function I linked to is working well for me. – fastzombies Jun 28 '17 at 13:41

Excel VBA Split on regexp

0 Answers0