Please try the following solution.

It is using tokenization via XML and XQuery.

Notable points:

• CROSS APPLY clause creates XML for each row.
• XQuery FLWOR expression is checking for numeric values followed by the 'Test' word.
• $pos variable holds a position for each word.

XML sample for ID=1

<root>
  <r>Random</r>
  <r>Text</r>
  <r>3</r>
  <r>Random</r>
  <r>568</r>
  <r>Text</r>
  <r>5.5</r>
  <r>Test</r>
  <r>Random</r>
  <r>Text</r>
  <r>345</r>
</root>

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(MAX));
INSERT @tbl (tokens) VALUES
('Random Text 3 Random 568 Text 5.5 Test Random Text 345'),
('Random Text 3 Test Text Random'),
('Random Text 777 Random Text');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = SPACE(1);

SELECT *
    , c.query('for $x in /root/r
        let $pos := count(/root/r[. << $x]) + 1
        return if (xs:decimal($x) instance of xs:decimal (: filter out non-digits :)
            and /root/r[$pos+1]/text()="Test") then $x
         else ()').value('.','VARCHAR(MAX)') AS result
FROM @tbl AS t
    CROSS APPLY (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(tokens, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t1(c)
ORDER BY ID;

Ouput

Just another option using a bit of JSON and the window function lead() over()

Example

DECLARE @YourTable Table (SomeCol VARCHAR(150));
INSERT @YourTable VALUES
('Random Text 3 Random 568 Text 5.5 Test Random Text 345'),
('Random Text 3 Test Text Random'),
('Random Text 777 Random Text');

Select A.SomeCol
      ,B.RetVal
 From  @YourTable A
 Outer Apply (
                Select RetVal
                 From  (
                        Select RetVal = Value
                              ,NxtVal = lead(Value,1) over (order by [Key])
                         From  OpenJSON( '["'+replace(string_escape(SomeCol,'json'),' ','","')+'"]' )
                       ) B1
                 Where NxtVal='Test'
                   and try_convert(money,RetVal) is not null
             ) B

Results

SomeCol                                                   RetVal
Random Text 3 Random 568 Text 5.5 Test Random Text 345    5.5
Random Text 3 Test Text Random                            3
Random Text 777 Random Text                               NULL

Given that the pattern (with the numbers before 'test' is consistent across all the string, here is the sql for extracting the number:

SELECT 
    CASE 
        WHEN CHARINDEX(' Test ', [String 1]) > 0 
        THEN SUBSTRING([String 1], CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))+1, CHARINDEX(' ', [String 1], CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))+1) - CHARINDEX(' ', [String 1], CHARINDEX(' Test ', [String 1]))-1)
        ELSE ''
    END AS [String 1],

    CASE 
        WHEN CHARINDEX(' Test ', [String 2]) > 0 
        THEN SUBSTRING([String 2], CHARINDEX(' ', [String 2])+1, CHARINDEX(' ', [String 2], CHARINDEX(' Test ', [String 2])) - CHARINDEX(' ', [String 2])-1)
        ELSE ''
    END AS [String 2],

    CASE 
        WHEN CHARINDEX(' Test ', [String 3]) > 0 
        THEN SUBSTRING([String 3], CHARINDEX(' ', [String 3])+1, CHARINDEX(' ', [String 3], CHARINDEX(' Test ', [String 3])) - CHARINDEX(' ', [String 3])-1)
        ELSE ''
    END AS [String 3]

Hope this helped!

You need to create a Scalar Function like this (I did this in SQL Server)

CREATE FUNCTION GetNumberBeforeStringTest
(
    @stringToParse varchar(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @testIndex INT = PATINDEX('%test%', @stringToParse);
    
    IF @testIndex = 0 RETURN NULL;

    DECLARE @s1 VARCHAR(100) = REVERSE(TRIM(SUBSTRING(@stringToParse, 0, @testIndex)))
    
    DECLARE @s2 VARCHAR(100) = REVERSE(TRIM(SUBSTRING(@s1, 0, CHARINDEX(' ', @s1))))

    IF TRY_CAST(@s2 AS decimal) IS NULL
        RETURN NULL;

    RETURN @s2;
END
GO

You use like this select dbo.GetNumberBeforeStringTest()

e.g. select dbo.GetNumberBeforeStringTest('Random Text 3 Random 568 Text 5.5 Test Random Text 345')

Result: 5.5