[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Trying to break text down to chunks that are 325 char long.

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
       Loop
        ' 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))
        End If
            sPart1 = "<start> " & Format(Now, "mm/dd/yy hh:mm") & sPart1 & "<cont...>"
Else
    sPart1 = Format(Now, "mm/dd/yy hh:mm - ") & sText
    

    End If
     
    SplitmyStr = UCase(sPart1)
     
End Function

Open in new window


Thanks!
0
Bruj
Asked:
Bruj
  • 4
  • 3
1 Solution
 
aikimarkCommented:
Is this a VB6, VB.Net, Access, or Excel application?

Wouldn't it be better to expand the field?

Generally speaking, this routine should return either an array or a collection with the split string parts.  The calling/invoking code would iterate through the return result, processing each item.

It would be helpful if you posted a sample string.
0
 
BrujAuthor Commented:
Is this a VB6, VB.Net, Access, or Excel application?
>I can use either Excel or Access. so it i VBA. We are coping notes that we made in one application to another. the first application has unlimited note field size, but the second only allows 325 chars  . So we need to chunk the data and put multiple notes to show what we have done (we actually need to enter from the last block then adding till th first block is added -reverse order)

Wouldn't it be better to expand the field?
>This would make sense, but the app I am pasting into is a major 3rd party app (ACSR) and we dont have control of that

Generally speaking, this routine should return either an array or a collection with the split string parts.  The calling/invoking code would iterate through the return result, processing each item.
>what I will be doing is to create a form that has a text box, then we would paste our notes into the the text box. We we submit the form, it would actual create a set of buttons that each button would have the data (and array would be propably the best to get the data from) and when one clicks the button, it would stuff the content for that string into the system clipboad (I have the code to stuff the buttons and then to the clipboard)

It would be helpful if you posted a sample string.
>We are technicians that suppor a varied arry of communications. Or notes would entail all of the steps and results we got while diagnosing the customer's service. so it really can be anything.  For testing, it could even be this reply as a sample piece of data
Usually we may have some confidential data n the actual notes)

Thanks!
Bruce
0
 
aikimarkCommented:
Here is a DoSplit() function that returns a collection of strings <= parmMaxLen in length.
Option Explicit

Function DoSplit(parmString, parmMaxLen) As Collection
    Dim lngStart As Long
    Dim lngSpacePosn As Long
    Dim colStrings As New Collection
    If Len(parmString) < parmMaxLen Then
        colStrings.Add parmString
    Else
        lngStart = 1
        Do
            lngSpacePosn = InStrRev(Mid(parmString, lngStart), " ", lngStart + parmMaxLen)
            If lngSpacePosn <> 0 Then
                colStrings.Add Mid(parmString, lngStart, lngSpacePosn - lngStart)
                lngStart = lngSpacePosn + 1
            Else
                lngSpacePosn = InStrRev(Mid(parmString, lngStart), " ")
                colStrings.Add Mid(parmString, lngStart, lngSpacePosn - 1)
                lngSpacePosn = InStrRev(parmString, " ")
                colStrings.Add Mid(parmString, lngSpacePosn + 1)
                Exit Do
            End If
        Loop Until lngSpacePosn = 0
    End If
    Set DoSplit = colStrings
End Function

Open in new window


I tested the function with this code, breaking the sentence into 20 character (or fewer) chunks and displaying the returned strings in the Immediate Window
Option Explicit

Sub drivesplit()
    Dim colS As Collection
    Dim vItem As Variant
    Set colS = DoSplit("Now is the time for all good men to come to the aid of their country", 20)
    For Each vItem In colS
        Debug.Print vItem
    Next
End Sub

Open in new window

0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
BrujAuthor Commented:
Thank you! It works fairly well. One little hiccup, if you trigger the
IF Len(parmString) < parmMaxLen Then
else statement
It does break it up, however, it seems it will always break the last segment down at the last word, no matter how long the statement is if it has no trailing space
My work around is is to add a space at the end

Function drivesplit(myStr As String, myLen As Long)

    Dim colS As Collection
    Dim vItem As Variant
       Set colS = DoSplit(myStr & " ", myLen)

  '  Set colS = DoSplit("Now is the time for all good men to come to the aid of their country", 20)
    For Each vItem In colS
        Debug.Print "|" & vItem
    Next
End Function

Open in new window


Is there a way to break at the puncuation as well? (years ago, I had seen something that did this, but I cant find it anymode) You would have a string of the different "delimiters" and it would break at the closest one.

Thanks!
Bruce
0
 
aikimarkCommented:
There is, but the code gets messy.  You have to define the eligible delimiters and exceptions have to be made if a delimiter might occur in the data or be treated in a special manner, such as a hyphen.
0
 
BrujAuthor Commented:
Thanks!
0
 
aikimarkCommented:
If you want to consider other delimiters, take a look at this article:
http:A_1480-How-to-Split-a-String-with-Multiple-Delimiters-in-VBA.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now