Sorry... missed the "MonetaryValue" column that you included in your code but not in your problem definition... this will do it...

SELECT s.RowID,

Type = MAX(CASE WHEN s.Posit =1 AND LEN(s.SplitString) =2 THEN s.SplitString ELSE NULL END),

Code = MAX(CASE WHEN s.Posit =2 AND LEN(s.SplitString)<=6 THEN s.SplitString ELSE NULL END),

MonetaryValue = MAX(CASE WHEN s.Posit =5 THEN s.SplitString ELSE NULL END)

FROM (--==== Derived table "s" splits all of the strings and marks the position (ordinal index) of each

SELECT RowID,

SplitString = SUBSTRING(' '+h.String+' ', t.N+1, CHARINDEX(' ', ' '+h.String+' ', t.N+1)-t.N-1),

Posit = t.N-LEN(REPLACE(LEFT(' '+h.String+' ',t.N), ' ', ''))

FROM dbo.Tally t

RIGHT OUTER JOIN --Necessary in case String is NULL

#Test h

ON SUBSTRING(' '+h.String+' ', t.N, 1) = ' '

AND t.N < LEN(' '+h.String+' ')

AND h.String NOT LIKE '%[_]%'

)s

WHERE s.Posit IN (1,2,5)

GROUP BY s.RowID