VBA split string sentences with multiple values

user_15191950 0
Viewed 70

My Excel raw data looks something like this:

;123456p,Roses and butterflies;;124456h,Violets are blue;

;123456d,Hello world;

  • Expected output:
  • Roses and butterflies
  • Violets are blue
  • Hello world

Trying to split the text sentences out only, for rows with multiple sentences I would need them in separate rows, is this at all possible? Below is what I tried.

Private Sub CommandButton1_click()
    Dim splitstring As String
    Dim myarray() As String
    splitstring = Worksheets("raw").Cells(1, 1).Value
    myarray = Split(splitstring, ";")
    For i = 0 To URound(myarray)

    Next

End Sub

Sub raw()

End Sub
vba
2 Answers

With Regular Expressions, you can populate Column B with the desired results ae below

Option Explicit

Private Sub CommandButton1_click()

Dim wSh As Worksheet
Dim rngStr As String, rngStrArr() As String, i As Long
Set wSh = Worksheets("raw")
Dim regEx As Object, mc As Object
Set regEx = CreateObject("vbscript.regexp")

regEx.Global = True
regEx.IgnoreCase = True

rngStr = Join(Application.Transpose(Application.Index(( _
        wSh.Range("A1:A" & wSh.Cells(wSh.Rows.Count, 1).End(xlUp).Row)), 0, 1)))
regEx.Pattern = ",([^;]+);"
Set mc = regEx.Execute(rngStr)
rngStr = ""
For i = 0 To mc.Count - 1
    rngStr = rngStr & mc(i)
Next i

rngStr = Replace(rngStr, ",", "")

rngStrArr = Split(rngStr, ";")

wSh.Range("B1").Resize(UBound(rngStrArr), 1).Value = Application.Transpose(rngStrArr)

End Sub

enter image description here

user_98080630

Try this:

Private sub SplitString()
Dim splitstring As String
Dim myarray() As String
splitstring = Cells(1, 1).Value
myarray = Split(splitstring, ",")

For i = 1 To UBound(myarray)
    MsgBox (myarray(i))
Next 
End Sub
Admin1
Related