Splitting a cell in a macro?

[Dagonini]

I have a column of cells that I would like to be able to split
automatically the problem is that there are two possible choices. The
cells begin with either Plan A or Plan B. I need to split Plan A
after 17 characters and Plan B after 25 characters.

Is there a way to write a bit of code that would be able to
differentiate Plan A and Plan B in Column C and split them so that
Plan A and Plan B stay in Column C but the other bit...0/7 or 30/30
moves into column D?

Thanks for any help in advance.

 

[Bernie Deitrick]

Mary,

You could try something like the macro below. I've assumed that your data in column C starts in row
2.

HTH,
Bernie
MS Excel MVP

Sub MarySplit()
Dim myRow As Long

myRow = Cells(Rows.Count, 3).End(xlUp).Row

Columns("D:E").Insert Shift:=xlToRight
Range("D2" & myRow).FormulaR1C1 = _
"=IF(LEFT(RC[-1],6)=""Plan A"",LEFT(RC[-1],17),LEFT(RC[-1],25))"
Range("E2:E" & myRow).FormulaR1C1 = "=SUBSTITUTE(RC[-2],RC[-1],"""")"
Range("D2:E" & myRow).Value = Range("D2:E" & myRow).Value
Columns("C:C").Delete Shift:=xlToLeft
Columns("C").EntireColumn.AutoFit
End Sub