I like the "split at last space" condition 'challenge'. A little more
"wordy" than your regex solution, but still a one-liner (my own
self-imposed restriction) VBA function...

Function Truncate(ByVal Source As String, _
Optional TrimAt As Long = 30000) As String
Truncate = RTrim$(Left(Source, InStrRev(Left(Source, TrimAt), ".") - _
(InStrRev(Left(Source, TrimAt), " ")) * _
(Not Left(Source, TrimAt) Like "*.*") - _
(Len(Left(Source, TrimAt))) * _
(Not Left(Source, TrimAt) Like "*[. ]*")))
End Function

Note that I still left the optional TrimAt argument so the OP can set it
to 500 (or any other value desired) in the calling formula.

And this is how I would write the function in "real life" (that is,
without forcing it to a one-liner)...

Function Truncate(ByVal Text As String, _
Optional TrimAt As Long = 30000) As String
Text = Left$(Text, TrimAt)
Truncate = Left$(Text, InStrRev(Text, "."))
If Len(Truncate) = 0 Then Truncate = RTrim(Left(Text, InStrRev(Text, "
")))
If Len(Truncate) = 0 Then Truncate = Text
End Function

Note: I changed some argument names to avoid using line continuations

Damn! The line wrapped at an 'unfortunate' spot (a blank) anyway. Here is
the same function renamed to Chop instead of Truncate; it should have each
statement fit on a single line without wrapping...

Function Chop(ByVal Text As String, _
Optional TrimAt As Long = 30000) As String
Text = Left$(Text, TrimAt)
Chop = Left$(Text, InStrRev(Text, "."))
If Len(Chop) = 0 Then Chop = RTrim(Left(Text, InStrRev(Text, " ")))
If Len(Chop) = 0 Then Chop = Text
End Function

Rick