how to split the data in one column to their respective columns?

[chinna reddy]

hi
Please help me in the following file.In the first sheet there will be instructions what to do.I will be thankful if some one helps.

 

[shrivallabha]

If VBA is acceptable then you can test below code on backup copy. It will generate the desired output in Input Sheet's empty columns starting from D to F. I have commented in the code for logic.

Public Sub TransposeInfo()
Dim i As Long, j As Long
For i = 2 To Range("C" & Rows.Count).End(xlUp).Row '\\ Loop through all cells
    Range("D" & i).Resize(1, 3).Value = "-"        '\\ Let's put default value
    If Range("A" & i).MergeCells Then              '\\ Check if it is merged cell or unmerged one
        '\\ Process multiple rows for merged cells
        For j = 0 To Range("A" & i).MergeArea.Cells.Count - 1
            If InStr(1, Range("C" & i + j).Value, "R", vbTextCompare) > 0 Then
                Range("D" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i + j).Value, "(", ""), ")", ""), "R", "")
            ElseIf InStr(1, Range("C" & i + j).Value, "M", vbTextCompare) > 0 Then
                Range("E" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i + j).Value, "(", ""), ")", ""), "M", "")
            ElseIf InStr(1, Range("C" & i + j).Value, "O", vbTextCompare) > 0 Then
                Range("F" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i + j).Value, "(", ""), ")", ""), "O", "")
            End If
        Next j
        i = i + j - 1
    Else
        '\\ One row at a time for unmerged cells
        If InStr(1, Range("C" & i).Value, "R", vbTextCompare) > 0 Then
            Range("D" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i).Value, "(", ""), ")", ""), "R", "")
        ElseIf InStr(1, Range("C" & i).Value, "M", vbTextCompare) > 0 Then
            Range("E" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i).Value, "(", ""), ")", ""), "M", "")
        ElseIf InStr(1, Range("C" & i).Value, "O", vbTextCompare) > 0 Then
            Range("F" & i).Value = "'" & Replace(Replace(Replace(Range("C" & i).Value, "(", ""), ")", ""), "O", "")
        End If
    End If
Next i
End Sub