CODEWITHSUNDEEP
excelvba
MK01111000
MK01111000

Reputation: 832

How do I split a string over multiple rows if the value is 4 numbers

The following will split the selected text value by the delimiter "1234"

Sub SPLIT()
    Dim x As Variant
        x = Split(Selection, "1234")
        With ActiveSheet
        For ii = LBound(x) To UBound(x)
            .Cells(1 + ii, 1).Value = x(ii)
        Next ii
    End With
End Sub

However, I'd like to split the text on the delimiter " #### " where the # is any given number. There should also be an empty space in front and at the end of the delimiter. I guess a regular expression is needed here, but I don't know how to do that in this case.

Example:
blabla 1234 blabla blablabla bla 7548, blablabl 7854 bla 154 blablabla

Desired outcome:
blabla
1234 blabla blablabla bla 7548, blablabl
7854 bla 154 blablabla

Upvotes: 0

Views: 81

Answers (2)

JvdV
JvdV

Reputation: 75900

You can work your way around it through regular expressions. Here is an example:

Sub Test()

Dim Str As String: Str = "blabla 1234 blabla blablabla bla 7548, blablabl 7854 bla 154 blablabla"
Dim newStr As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = " (\d{4} )"
    newStr = .Replace(Str, Chr(10) & "$1")
End With
Debug.Print newStr

End Sub

Also, see an online demo where the pattern would mean:

  • - A literal space.
  • ( - Open 1st capture group:
    • \d{4} - Four digits ranging from 0-9.
    • - A literal space.
    • ) - Close 1st capture group.

Now we can simply replace the pattern with a newline character followed by a backreference to whatever we captured in our 1st capture group.

Our final result will look like:

enter image description here

EDIT:

If your goal was to split into different elements, you can try:

Sub Test()

Dim Str As String: Str = "blabla 1234 blabla blablabla bla 7548, blablabl 7854 bla 154 blablabla"
Dim newStr() As String
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = " (\d{4} )"
    newStr = Split(.Replace(Str, "|$1"), "|")
End With

End Sub

enter image description here

Upvotes: 3

Warcupine
Warcupine

Reputation: 4640

This should do it. I put your string in A1 and output to B1:B3

    Dim mystr As String
    Dim strarr As Variant
    
    With Sheet1
        mystr = .Cells(1, 1).Value
        
        strarr = Split(mystr, " ")
        
        Dim ele As Variant
        Dim outputrange As Range
        Set outputrange = .Cells(1, 2)
        For Each ele In strarr
            If Len(ele) = 4 And IsNumeric(ele) Then
                Set outputrange = outputrange.Offset(1)
            End If
            If outputrange.Value = "" Then
                outputrange.Value = ele
            Else
                outputrange.Value = outputrange.Value & " " & ele
            End If
        Next ele
    End With

Upvotes: 2

Related Questions