How to split string using VBA?

[dmadhup]

For example, I have following string

SPLICE 3_CON 195_INTERLOCK_18_BROWN I am trying to write VBA code to split it into five and put in different columns like: SPLICE 3 CON 195 INTERLOCK 18 BROWN <tbody> </tbody>

<tbody>
</tbody>

Any help is appreciated.

 

[Fluff]

Something like

Range("B2").Resize(, UBound(Split(Range("A2"), "_")) + 1).Value = Split(Range("A2"), "_")

 

[mumps]

This macro assumes you have headers in row 1 and that you strings are in column A.

Sub SplitString()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim lColumn As Long
    Dim rng As Range
    Dim vString As Variant
    Dim i As Long
    For Each rng In Range("A2:A" & LastRow)
        vString = Split(rng, "_")
        For i = LBound(vString) To UBound(vString)
            lColumn = ActiveSheet.Cells(rng.Row, Columns.Count).End(xlToLeft).Column + 1
            Cells(rng.Row, lColumn) = vString(i)
            lColumn = lColumn + 1
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub