Maybe ...
Option Explicit
Sub SplitData()
Dim aAll As Variant, aSplit As Variant
Dim aORM() As String, aSGL() As String, aBRC() As String
Dim iAll As Long, iSplit As Long
Dim sORM As String, sSGL As String, sBRC As String
With ActiveSheet
'bring in as Nx1 array
aAll = Application.WorksheetFunction.Transpose(Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value)
'join number with string
For iAll = LBound(aAll) To UBound(aAll)
aAll(iAll) = Replace(aAll(iAll), "|ORM", Chr(1) & "ORM")
aAll(iAll) = Replace(aAll(iAll), "|SGL", Chr(1) & "SGL")
aAll(iAll) = Replace(aAll(iAll), "|BRC", Chr(1) & "BRC")
Next iAll
'ready output arrays
ReDim aORM(LBound(aAll) To UBound(aAll))
ReDim aSGL(LBound(aAll) To UBound(aAll))
ReDim aBRC(LBound(aAll) To UBound(aAll))
'split data
For iAll = LBound(aAll) To UBound(aAll)
If Len(aAll(iAll)) > 0 Then
sORM = vbNullString
sSGL = vbNullString
sBRC = vbNullString
aSplit = Split(aAll(iAll), "|")
For iSplit = LBound(aSplit) To UBound(aSplit)
Select Case Right(aSplit(iSplit), 3)
Case "ORM"
sORM = sORM & aSplit(iSplit) & "|"
Case "SGL"
sSGL = sSGL & aSplit(iSplit) & "|"
Case "BRC"
sBRC = sBRC & aSplit(iSplit) & "|"
End Select
Next iSplit
If Len(sORM) > 0 Then aORM(iAll) = Replace(sORM, Chr(1), "|")
If Len(sSGL) > 0 Then aSGL(iAll) = Replace(sSGL, Chr(1), "|")
If Len(sBRC) > 0 Then aBRC(iAll) = Replace(sBRC, Chr(1), "|")
End If
Next iAll
'put back
.Cells(1, 2).Resize(UBound(aAll), 1).Value = Application.WorksheetFunction.Transpose(aORM)
.Cells(1, 3).Resize(UBound(aAll), 1).Value = Application.WorksheetFunction.Transpose(aSGL)
.Cells(1, 4).Resize(UBound(aAll), 1).Value = Application.WorksheetFunction.Transpose(aBRC)
End With
End Sub