CASE WHEN (CHARINDEX(' ',ColName)=0) 
THEN 'Unknown' 
ELSE SUBSTRING(ColName,CHARINDEX(' ',ColName)+1,
    LEN(ColName) - 
    LEN(LEFT(ColName, CHARINDEX(' ',ColName))) - 
    LEN(RIGHT(ColName, CHARINDEX(' ',ColName) - 1)))
END AS [Middle_Value]

DECLARE @spitme TABLE (Item varchar(40));
INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT s.Item
FROM @spitme val
CROSS APPLY test.[dbo].[DelimitedSplit8K](val.Item,' ') s
WHERE ItemNumber = 2

DECLARE @spitme TABLE (Item varchar(40));
INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT STUFF(STUFF(Item, 1, CHARINDEX(' ', Item), ''), CHARINDEX(' ', STUFF(Item, 1, CHARINDEX(' ', Item), '')), LEN(Item), '')
FROM @spitme

DECLARE @spitme TABLE (Item varchar(40));
INSERT @spitme VALUES ('HEYE-B Euro-IPA 69793'), ('HEYE-B RFE-IPA 70940');

SELECT SUBSTRING(Item, CHARINDEX(' ',Item)+1, LEN(Item)-(CHARINDEX(' ',Item)+CHARINDEX(' ',REVERSE(Item))))
FROM @spitme