How to trim a string to the last "."

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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.

That is still a feature of the UDF I wrote to test the regex; it's similar to
the previous one I posted with ...Optional NumChars as Long = 500 ...

but the regex the UDF constructs substitutes NumChars for the 500 in the one I
posted.

By the way, there is a program called RegexBuddy which makes designing and
debugging regexes quite simple.
--ron

 

[Rick Rothstein \(MVP - VB\)]

By the way, there is a program called RegexBuddy which makes

designing and debugging regexes quite simple.

I looked RegexBuddy up via Google... that looks like an interesting product.
I bookmarked it in the event I ever decide to get back into Regular
Expression. Thanks for mentioning it.

Rick