VBA Using Regular Expressions to Split a String

Question

Hoping someone may be able to offer a better approach to what I am doing. Unfortunately the data and code sit on client systems so I can't share them.

I have this long string which was previously a data table and need to split up the values into rows and columns again. The system is heavily locked down so I am limited to using VBA. The best way I can think of is to use regular expressions to identify columns and rows. I've set up my regular expression object and executed against the input string, have all the matches I need which is fine. The problem is that if I do

re = CreateObject("VBScript.RegExp")
re.pattern = mypattern
re.split(myString)

As far as I can see there is no way to retain the values I am splitting on. In some cases I want to split in the middle of the regex string anyway.

The most promising solution I think is to do

re = CreateObject("VBScript.RegExp")
re.pattern = mypattern
Set matches = re.execute(myString)
for each match in matches:
    'do something with match.firstindex

I considered just inserting delimiters and then using split. Unfortunately VBA seems to have no method to insert a character into a string it looks a bit clunky to use the firstindex.

Does anyone have any thoughts on better approaches? Much appreciated if so.

Without any - even dummy - data it is not clear how to help. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Aug 1, 2017 at 19:52
Pretty sure matches needs to be an object reference and thus assigned using the Set keyword. Question: why aren't you using early-binding to work with API's you're unfamiliar with? — Mathieu Guindon
– Mathieu Guindon, Commented Aug 1, 2017 at 20:50
Thanks for your thoughts. I will try to share a better description of the data/string later. I hadn't come across the term early binding but looks like I am using it. I just typed he above code in the stackoverflow editor as I don't have access to the code I am actually using. Yes, I think set keyword is required. — soundofsilence
– soundofsilence, Commented Aug 2, 2017 at 17:30

pgSystemTester · Accepted Answer · 2017-08-02 20:53:40Z

1

You actually can insert characters into a string. Depends on your definition of "clunky" but here's an example:

ActiveCell.Characters(5, 1).Insert (" | ")

This will put a " | " starting at the fifth character of the cell. You might need to identify some positions using find or some loops through the cell characters, but I think that might get you on your way.

UPDATED WITH STRING EDIT This is just my preference but editing string doesn't seem too clunky. You can use this setup to probably get what you want:

Sub StringSlicerSample()
Dim teststring As String, Separater As String, tangoTxt As String, BeforeTXT As String, AfterTxt As String, MidTxt As String
Dim Position_To_Slice As Integer

teststring = "xxxbangyyy"    
Separater = " | " 'can be whatever you want as separator    
tangoTxt = "bang" 'text to look for that you want use for split
Position_To_Slice = 1 'If you want to split somewhere in between, lets say after the "b"
'put =1 as that's the first position in "bang"

'Insert separator before
BeforeTXT = Mid(teststring, 1, InStr(1, teststring, tangoTxt) - 1) & Separater & Mid(teststring, InStr(1, teststring, tangoTxt), Len(teststring))

'Insert after
AfterTxt = Mid(teststring, 1, InStr(1, teststring, tangoTxt) + Len(tangoTxt) - 1) & Separater & Mid(teststring, InStr(1, teststring, tangoTxt) + Len(tangoTxt), Len(teststring))

'Split inbetween based on position to slice
MidTxt = Mid(teststring, 1, InStr(1, teststring, tangoTxt) + Position_To_Slice - 1) & Separater & Mid(teststring, InStr(1, teststring, tangoTxt) + Position_To_Slice, Len(teststring))


MsgBox BeforeTXT, Title:="Before Example"
MsgBox AfterTxt, Title:="After Example"
MsgBox MidTxt, Title:="Sliced in position " & Position_To_Slice

End Sub

edited Aug 2, 2017 at 20:53

answered Aug 1, 2017 at 20:05

pgSystemTester

10k2 gold badges28 silver badges59 bronze badges

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

3 Comments

soundofsilence Over a year ago

Thanks, that's useful. I was looking at adding to a VBA string rather than directly in a cell. I could change my approach a bit to make use of his method though.

pgSystemTester Over a year ago

See updated answer if you want to use string. Seems not too complex?

soundofsilence Over a year ago

I'll accept as the answer, I think this is the best approach I can envisage. +1 for making a sensible guess at the type of data I was working with

Collectives™ on Stack Overflow

VBA Using Regular Expressions to Split a String

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related