Here is the macros to achieve the same.
Note...
1. Find and replace ", " ( comma and space in Order# with "/").
2. if sheet3 is not there then insert sheet3.
let me know if the code is working or not
Sub frmt()
Dim start_at As Integer
Dim stop_at As Integer
Dim no_of_occ As Integer
Dim row_counter As Integer
Cells(2, 4).Activate
row_counter = 2
Do While ActiveCell.Value <> ""
no_of_occurance = Len(WorksheetFunction.Substitute(ActiveCell.Value, "/", " ")) - Len(ActiveCell.Value)
start_at = 1
For i = 0 To no_of_occurance
If i = no_of_occurance Then
stop_at = Len(ActiveCell.Value)
Else
stop_at = Application.WorksheetFunction.Search("/", ActiveCell.Value, start_at)
End If
Sheet3.Cells(row_counter, 1) = ActiveCell.Offset(0, -3)
Sheet3.Cells(row_counter, 2) = ActiveCell.Offset(0, -2)
Sheet3.Cells(row_counter, 3) = ActiveCell.Offset(0, -1)
Sheet3.Cells(row_counter, 4) = Mid(ActiveCell.Value, start_at, stop_at - 1)
Sheet3.Cells(row_counter, 5) = ActiveCell.Offset(0, 1)
row_counter = row_counter + 1
start_at = stop_at + 1
Next i
ActiveCell.Offset(1).Activate
Loop
End Sub
Shijesh Kumar
Bangalore
Bookmarks