Macro to separate 1 column into multiple columns

[dac1517]

In column BN I have data in the format 1000A-01-100. I would like to separate this into 3 columns using the "-" as the point of separation. Below is the code I found on the forums that I tried to use for this but I keep getting a runtime error "1004".

Sub SplitBrokerData()
 
    Dim arr As Variant, i As Long
 
    For i = 1 To Range("BN1").End(xlDown).Row
        arr = Split(Cells(i, 1), "-")
        Cells(i, 2).Resize(, UBound(arr) + 1) = arr
    Next
 
End Sub

Any help would be appreciated

 

[Andrew Poulsom]

Have you tried Data|Text To Columns with - as the Delimiter?

 

[Yahya]

Try this code

Sub SplitIt()
Dim MyArr As Variant, LR As Long, c As Range
LR = Range("BN" & Rows.Count).End(xlUp).Row
For Each c In Range("BN1:BN" & LR)
    MyArr = Split(c, "-")
    For i = LBound(MyArr) To UBound(MyArr)
        c.Offset(, i + 1).Value = MyArr(i)
    Next i
Next c

End Sub

 

[dac1517]

That macro works perfectly. Thanks