The direct translation is:

RIGHT([Field6], LEN([Field6]) - CHARINDEX('Unit', [Field6]) + 1)

However, the the following is likely to be slightly more efficient.

Just change the final argument of the SUBSTRING function to be the maximum length of the [Field6] field.

SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100)