Excel - Split cell using regex in VBA, keep delimiter

Question

I have a row of cells in Excel that follow the following syntax:

randomtext VALUES (randomnumber, randomtext,

I have a VB script to split the text using regular expressions:

Public Function SplitRe(text As String, pattern As String, Optional ignorecase As Boolean) As String()
  Static re As Object
  If re Is Nothing Then
    Set re = CreateObject("VBScript.RegExp")
    re.Global = True
    re.MultiLine = True
  End If
  re.ignorecase = ignorecase
  re.pattern = pattern
  SplitRe = Strings.Split(re.Replace(text, vbNullChar), vbNullChar)
End Function

The delimiter I'm using: (VALUES)+(\s+)+(.)+\d+(,)

This results in the text being split as:

randomtext

What I'm trying to achieve is to split the text after the script finds the delimiter, creating this:

randomtext VALUES (randomnumber,

But I can not find a way to properly augment my script to do so. Anyone see a solution?

Like your use of VBScript.RegExp as a Static object. Can you add sample data together with expected results? — user4039065
– user4039065, Commented Aug 17, 2017 at 14:25
Unless I'm missing something, you don't need RE for this, you could use Split? — SJR
– SJR, Commented Aug 17, 2017 at 14:27

LS_ᴅᴇᴠ · Accepted Answer · 2017-08-17 15:19:39Z

1

Without regular expressions neither splitting:

Faster:

 Left(text, InStrRev(text,",")) 'Truncate string after last comma

Safer (assuming VALUES ( doesn't occur in first randomtext):

 Left(text, InStr(InStr(text,"VALUES ("),text,",")) 'Truncate string after comma which is after "VALUE ("

edited Aug 17, 2017 at 15:19

answered Aug 17, 2017 at 14:36

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

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

6 Comments

Tampert Over a year ago

The randomtext before VALUES differs per row, and contains symbols '(' and ',' That's why I wanted to play it safe and use regex

LS_ᴅᴇᴠ Over a year ago

and how would a your regex process "VALUE (" VALUE (1.45, "some text"?

Tampert Over a year ago

I like your Left(text, InStr(InStr(text,"VALUES ("),",")) solution, but somehow it's not giving me output.

LS_ᴅᴇᴠ Over a year ago

Is VALUES case sensitive and space is regular space?

Tampert Over a year ago

Case sensitive and regular space, yes

|

Collectives™ on Stack Overflow

Excel - Split cell using regex in VBA, keep delimiter

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related