If you use the SUBSTRING function, just set the 3rd argument of the SUBSTRING function to be the same as the maximum number of characters that the [Field6] column can contain.
SUBSTRING([Field6], CHARINDEX('Unit', [Field6]), 100)
Also, how can I convert this:Len(Left([Field3],InStr(1,[Field3]," or")-1)) Thank you
A translation of this Access expression is:
LEN(SUBSTRING([Field3], 1, CHARINDEX(' or', [Field3]) - 1))