4

edit: wow, thanks for so many suggestions, but I wanted to have a regexp solution specifically for future, more complex use.

I need support with splitting text string in VBA Excel. I looked around but solutions are either for other languages or I can't make it work in VBA.

I want to split words by single slashes only:

text1/text2- split
text1//text2- no split
text1/text2//text3 - split after text1

I tried using regexp.split function, but don't think it works in VBA. When it comes to pattern I was thinking something like below:

(?i)(?:(?<!\/)\/(?!\/))

but I also get error when executing search in my macro while it works on sites like: https://www.myregextester.com/index.php#sourcetab

Trm
  • 479
  • 2
  • 9
  • 22

4 Answers4

7

You can use a RegExp match approach rather than split one. You need to match any character other than / or double // to grab the values you need.

Here is a "wrapped" (i.e. with alternation) version of the regex:

(?:[^/]|//)+

Here is a demo

And here is a more efficient, but less readable:

[^/]+(?://[^/]*)*

See another demo

Here is a working VBA code:

Sub GetMatches(ByRef str As String, ByRef coll As collection)

Dim rExp As Object, rMatch As Object

Set rExp = CreateObject("vbscript.regexp")
With rExp
    .Global = True
    .pattern = "(?:[^/]|//)+"
End With

Set rMatch = rExp.Execute(str)
If rMatch.Count > 0 Then
    For Each r_item In rMatch
        coll.Add r_item.Value
        Debug.Print r_item.Value
    Next r_item
End If
Debug.Print ""
End Sub

Call the sub as follows:

Dim matches As New collection
Set matches = New collection
GetMatches str:="text1/text2", coll:=matches

Here are the results for the 3 strings above:

1. text1/text2
 text1
 text2

2. text1/text2//text3
 text1
 text2//text3

3. text1//text2
 text1//text2
Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • 1
    Wow, this is brilliant and by a test run it looks exactly what i need. Have been bashing my head for half a day. Match is a new concept for me so I will need to reverse engineer to fully understand it. Thanks a lot! this will be very useful learning experience. – Trm Sep 15 '15 at 15:15
  • If you have other issues with adapting oother regexps for VBA containing lookbehinds, look for "lookbehind workaround". Note that almost always you will need to translate part of a pattern to code. Use JavaScript compliant online regex testers. As for splitting, it can be replaced with matching in the majority of cases, and is usually much more readable. – Wiktor Stribiżew Sep 15 '15 at 15:25
  • a quick question. I now remember I used similar approach to iterate through matches before, but I got the match instead of values between them. Example: For intIndex = 1 To objMatch.Count (I got returned only slash), how come in your sample it returns text? Does it have to do "For Each" statement? – Trm Sep 15 '15 at 15:26
  • You got a slash because `objMatch` contained matches. That is what a regex is for: to match specific text patterns. When you split, you still match some (sequence of) characters that serve as a separator for a given input. `For Each` just helps iterate through the matches. Or Submatches, but you do not need them since you do not have capturing groups in the pattern I suggest. – Wiktor Stribiżew Sep 15 '15 at 16:24
  • I hope you don't mind asking, but if I wanted to use same logic, but also consider word "and" in the logic? So it would split forward slash and/or by word "and". I was thinking using negative look ahead, but can't seem to make it work `(?!\sand)[^\/]+(?:\/\/[^\/]*)*` – Trm Sep 16 '15 at 13:10
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89814/discussion-between-trm-and-stribizhev). – Trm Sep 16 '15 at 13:46
1
Public Sub customSplit()
    Dim v As Variant

    v = Split("text1/text2//text3", "/")
    v = Replace(Join(v, ","), ",,", "//")

    Debug.Print v   '-> "text1,text2//text3"
End Sub

or

Replace(Replace("text1/text2//text3", "/", ","), ",,", "//")   '-> "text1,text2//text3"
paul bica
  • 10,557
  • 4
  • 23
  • 42
  • thanks for suggestion, but I was wondering if there is regex solution available. I may need more dynamic splits in the future where replace function won't just cut it. – Trm Sep 15 '15 at 15:01
0

Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.

Ashwith Ullal
  • 263
  • 3
  • 10
0

Text to columns will work. Another option, if you want to keep the original value, is to use formulas: in B1

=left(a1,find(":",a1)-1)

in C1

=mid(a1,find(":",a1)+1,len(a1))
Ashwith Ullal
  • 263
  • 3
  • 10