I have an application that has a notes field. The notes field accepts 325 chars.
Sometimes we need to cut and paste longer blocks of text from another app.
Ideally, what I would like to do if the block is over 310 char is to break the text down to smaller blocks, the first with "<Start> mm/dd/yy " then the body of the text.
If longer than 300, then the first block wold be "<Start> mm/dd/yy " part1 of body <cont...>
the next section would be <...cont> part2 of the body (and either <cont...> or <END> depending on if more than 2 sections or not
I would like to assign each block to a varible for other processing
I have code that will work OK for 2 peices of code, but I need it to step through each resulting block and break that down (this should be pretty simple) but I would like to break at other "triggers" besides space (so it would break the sentence at a punctuation mark as well)
Function SplitmyStr(sText As String) As String
'Dim sText As String ' The text we want to truncate
Dim sPart1 As String ' First part of the chopped string
Dim sPart2 As String ' Second part of the chopped string
Dim iChop As Integer ' The point we ideally want to chop it at
Dim iPos As Integer ' Current position in string
iChop = 300
' Grab the text we want to look at
' sText = Cells(1, 1).Text
' If it's too long
If Len(sText) > iChop Then
iPos = iChop
' Move back through the string until we
' find a space or reach the start
Do While Mid(sText, iPos, 1) <> " " And iPos > 0
iPos = iPos - 1
' If we didn't reach the start, iPos will be the
' position of the first space BEFORE our chop point
If iPos > 0 Then
' Split the string in two
sPart1 = Trim(Left(sText, iPos))
sPart2 = Trim(Right(sText, Len(sText) - iPos))
sPart1 = "<start> " & Format(Now, "mm/dd/yy hh:mm") & sPart1 & "<cont...>"
sPart1 = Format(Now, "mm/dd/yy hh:mm - ") & sText
SplitmyStr = UCase(sPart1)