To delete comma when split the text by using VBA code

[Kenor]

Hi,

I use below code to split the text without any comma,

Sub RSplit()
For I = 2 To Cells(Rows.Count, 3).End(xlUp).Row
mysplit = Split(Cells(I, 3).Value & " #", " ", , vbTextCompare)
Cells(I, 3).Resize(1, UBound(mysplit)).Value = mysplit
Next I
End Sub

But if text have comma, how to edit the code ?
By manual, only need to choose Delimeted (for character such as comma ).

Can help me?

Thanks

 

[Anthony47]

Try updating this instruction:

mySplit = Split(Replace(Cells(I, 3).Value, ", ", " ", , , vbTextCompare) & " #", " ", , vbTextCompare)

Bye

 

[mart37]

sorry mistake

 

[mart37]

new one:

mysplit = Split(Cells(I, 3).Value & ", #", ", ", , vbTextCompare)

 

[Anthony47]

Mart37, this is an evolution of a previous thread: Split text to multiple columns using VBA code
So i guess that the comma could be there or could be missing

Bye

 

[Kenor]

Try updating this instruction:

mySplit = Split(Replace(Cells(I, 3).Value, ", ", " ", , , vbTextCompare) & " #", " ", , vbTextCompare)

Bye

I already try, comma has disappeared after split.
But the column header also change after split.

 

[Anthony47]

Maybe the solution proposed by @mart37, see message #4, is more appropriate (if your text always contains both comma + space)...

Or start splitting from row 3 rather then 2:

For I = 3 To Cells(Rows.Count, 3).End(xlUp).Row

Bye