additional code would be needed in order to return the whole string of
text
(assuming that is what the OP would want to happen when no periods are
found
within the text).
...

Less code in my example: just omit the "?" :-)

A couple of points.

Your "code" is not really shorter if you include the VBA code required for
the
UDF.

That was going roughly going to be my answer when I first read Bernd's
response; but I think, given the trimmed portion of the previous responses
in his posting and leaving aside the 500 character maximum truncation
requested by the OP, he was simply just addressing my comment about needing
*additional* code for my function to be able to return the entire text
string whereas to do the same with his code only required removing the
question mark. Yes, there is the question of the needed UDF code to make his
formula line work as well as the 500 character truncation issue; but I'm
pretty sure Bernd's posting was meant to focus in on this much narrower
issue without addressing anything else.

Rick


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