Code for splitting string at a specific character

[kbishop94]

sorry for such a rudimentary question, but I just cant seem to get the right combination of mid/left/find in my code

What I'm trying to do is to take each string in column "L" (starting at L1 and ending with the last row found in that column) and at the " : " character, spilt the string with the number value going into the immediate column to the right ("M") and the string (less the spaces) going to the next column over from that (column "N".) Thanks in advance for any help!

 

[EXCEL MAX]

Try this...

Sub SplitToMN()

    Dim vA1, vA2
    
    With ActiveSheet
        vA1 = .Range("L1", .Cells(Rows.Count, "L").End(xlUp))
        ReDim vA2(1 To UBound(vA1), 1 To 2)
        For vN = 1 To UBound(vA1)
            vA2(vN, 1) = Split(vA1(vN, 1), ":")(0)
            vA2(vN, 2) = Trim(Split(vA1(vN, 1), ":")(1))
        Next vN
        .Range("M1").Resize(UBound(vA2), 2) = vA2
    End With
    
End Sub

 

[kbishop94]

Thank you, EXCEL MAX. that worked perfectly

 

[EXCEL MAX]

You are welcome, thanks for feedback.