Hello Rick,
...
...
Less code in my example: just omit the "?"
Regards,
Bernd
Bernd,
A couple of points.
Your "code" is not really shorter if you include the VBA code required for the
UDF.
But, more importantly, I don't believe your code will work with a multiline
text string because of the limitations of the VBScript flavor of regex.
In particular, there is no provision, in VBScript to enable <dot matches
newline>.
OK, I just downloaded the UDF and tested it and, indeed, it does not work on
multiline text strings.
If you want to use regular expressions for this exercise, I would suggest the
following UDF with a regex constructed according to the maximum length of the
string (500 in this case, but there is an optional argument to change that:
=========================================
Option Explicit
Function reTruncateAtDot(str As String, _
Optional NumChars As Long = 500) As String
Dim re As Object, mc As Object
Dim sPat As String
sPat = "^[\s\S]{1," & NumChars & "}\."
Set re = CreateObject("vbscript.regexp")
re.Pattern = sPat
Set mc = re.Execute(str)
reTruncateAtDot = mc(0)
End Function
==============================
The OP could use either:
=reTruncateAtDot(A1) which would truncate at the last dot prior to the 501st
character, or use an optional NumChars to change that.
The regex with the 500 characters "hard-coded" would look like:
"^[\s\S]{1,500}\."
If you wanted to use your formula, and have it work with multiline text
strings, then try:
=regexpreplace(A1,"^([\s\S]*\.)?[\s\S]*$","$1")
But you'd still need the second step to trim to 500 characters.
Oh, and in my contribution, since the OP did not specify what he wants to
happen if there are no dots in the first 500 characters, a VALUE error will be
returned. That, obviously, could be changed depending on the OP's wishes.
--ron
