Split cells with comma and space

[manju 3]

Hi Team, I am trying to split the cells if comma and space . I tried below code.Its spliting for space now. How can i change it so that it will check for multiple(both space and comma)

Dim splitvals As Variant
Dim totalVals As Long

Set sh1 = ThisWorkbook.Sheets(1)
Set sh2 = ThisWorkbook.Sheets(2)

sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
splitvals = Split(sh1.Cells(j, 2), " ")
For i = LBound(splitvals) To UBound(splitvals)
lrow2 = sh2.Range("B65356").End(xlUp).Row
lrow3 = sh2.Range("A65356").End(xlUp).Row
sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i

Next j

Even i tried:

splitvals = Split(sh1.Cells(j, 2), ", " " ")

Please suggest me . Thanks Manju

[Lost User]

Most method calls operate on objects, but your Split call does not appear to be connected to any object. Where is the documentation for that method?

[manju 3]

Dim splitvals As Variant
Dim totalVals As Long
Dim myData As Workbook
Set myData = Workbooks.Open("D:\Test.xlsx")

Set sh1 = myData.Sheets(1)
Set sh2 = myData.Sheets(2)
sh2.Cells.Clear
lrow1 = sh1.Range("A65356").End(xlUp).Row

For j = 2 To lrow1
splitvals = Split(sh1.Cells(j, 2), ",")

For i = LBound(splitvals) To UBound(splitvals)
    lrow2 = sh2.Range("B65356").End(xlUp).Row
    lrow3 = sh2.Range("A65356").End(xlUp).Row
    sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
    sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i   

Next j

Thanks Sharan

[Lost User]

That is just a repeat of your question. I asked you where the Split method is defined.

[Richard Deeming]

It's a built-in VBA method: Split function (Visual Basic for Applications) | Microsoft Docs[^]

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[Maciej Los]

Quote:

How can i change it so that it will check for multiple(both space and comma)

You can't pass multiple delimiters into VBA Split function. You have to call Split function again:

For j = 2 To lrow1
splitvals = Split(sh1.Cells(j, 2), " ")
For i = LBound(splitvals) To UBound(splitvals)
lrow2 = sh2.Range("B65356").End(xlUp).Row
lrow3 = sh2.Range("A65356").End(xlUp).Row
sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i

splitvals = Split(sh1.Cells(j, 2), ",")    
For i = LBound(splitvals) To UBound(splitvals)
    lrow2 = sh2.Range("B65356").End(xlUp).Row
    lrow3 = sh2.Range("A65356").End(xlUp).Row
    sh2.Cells(lrow3 + 1, 1) = sh1.Cells(j, 1)
    sh2.Cells(lrow3 + 1, 2) = splitvals(i)
Next i   

Next j