CODEWITHSUNDEEP
regexvbasplitdelimiter
soundofsilence
soundofsilence

Reputation: 339

VBA Using Regular Expressions to Split a String

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.

Upvotes: 0

Views: 1101

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9897

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

Upvotes: 1

Related Questions