Split a paragraph of text into up to 10 x 30 or 50 character cells without splitting words

[ukmike007]

Hi All

I have done a search of the forum to try and find an answer but can't seem to find one - sorry if it is out there!

I am trying to create a purchase order template for non-excel users. The final requirement of this is to allow the managers to input free text giving the supplier instructions without them having to split the text into the required 30 & 50 character limits per line that the order needs to contain.

I want to give them a box with data validation to limit the typed length to say 350 characters, and then have that text broken into alternating 30 & 50 character fields to raise the purchase order without breaking words. 350 is an abitrary number based on a sensible paragraph size - any further text could start another 350 character section and be split in the same way.

As an example my second paragraph of text would be split into:

I am trying to create a
purchase order template for non-excel users. The
final requirement of this is
to allow the managers to input free text giving
the supplier instructions
without them having to split the text into the
required 30 & 50 character
limits per line that the order needs to contain.

Many thanks!

Mike

 

[AD_Taylor]

Give this code a go:

Option Explicit


Sub SeparateOrders()
    Dim rngChk      As Range
    Dim rngC        As Range 'Loop Counter
    
    Set rngChk = Selection
    
    For Each rngC In rngChk.Cells
        rngC.Value = HardReturnStrAtIndex(rngC.Value, 30, 50)
    Next rngC
End Sub




Function HardReturnStrAtIndex(str As String, index1 As Integer, Optional index2 As Integer = 0) As String
    Dim strSplit()  As String
    Dim strTemp     As String
    Dim strOut      As String
    Dim index       As Integer
    
    Dim i           As Integer 'Loop Counter
    
    strSplit = Split(str, " ")
    strTemp = strSplit(LBound(strSplit))
    index = index1
    
    For i = (LBound(strSplit) + 1) To UBound(strSplit)
        If Len(strTemp & " " & strSplit(i)) >= index Then
            strOut = strOut & strTemp & Chr(10)
            strTemp = strSplit(i)
            
            If index = index1 Then
                index = index2
            Else
                index = index1
            End If
        Else
            strTemp = strTemp & " " & strSplit(i)
        End If
    Next i
    
    strOut = strOut & strTemp
    HardReturnStrAtIndex = strOut
End Function

You can either type the function directly into an adjacent cell, for example =HardReturnStrAtIndex(A1, 30, 50), and get the output that way or you can use the macro I've provided. If you use the macro you'll need to make a selection first and then run it as the code runs on that particular selection of cells.

HTH!

 

[ukmike007]

Unfortunately that does not solve the problem - yes I agree it splits the text at the required points, but I need my output to be populated into different fields.

This will allow the admin team to copy and paste straight into our finance application.

Ideally I also want to try and do this without visual basics due to the added complication that if macros are not enabled it will not automatically work & giving my employer's high security settings, Macros are often a problem. This can be worked around by training all the staff if necessary though!

Would there be a solution here to insert a new character such as '~' to define the field breaks and then use a search or find function to extract the result?

 

[AD_Taylor]

There is potentially a way to do it by inserting tildes or another character and the using something like SUBSTITUTE to transform these into line breaks, however the insertion of the characters would still need to be done via VBA. There is no formula way that I know of that would allow characters or line breaks to be inserted at the correct points.

I'm afraid that code is the best I can offer.

 

[hiker95]

ukmike007,

See if the following will help you:

By Rick Rothstein
Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line

 

[Rick Rothstein]

ukmike007,

See if the following will help you:

By Rick Rothstein
Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line

Unfortunately my article will not do what the OP has asked for.

 

[Rick Rothstein]

Unfortunately that does not solve the problem - yes I agree it splits the text at the required points, but I need my output to be populated into different fields.

Not sure how your data is arranged or which cells you want the output to go to, so this subroutine may need to be modified (see description after code)...

Sub WrapTextAlternating(CellRef As Range, MaxChars1 As Long, MaxChars2 As Long)
  Dim Space As Long, Text As String, TextMax As String, WrapText As String
  Dim Index As Long, Maxes(0 To 1) As Variant, Lines() As String
  Maxes(0) = MaxChars1
  Maxes(1) = MaxChars2
  Text = CellRef.Value
  Index = 0
  Do While Len(Text) > Maxes(Index)
    TextMax = Left(Text, Maxes(Index) + 1)
    If Right(TextMax, 1) = " " Then
      WrapText = WrapText & RTrim(TextMax) & vbLf
      Text = Mid(Text, Maxes(Index) + 2)
    Else
      Space = InStrRev(TextMax, " ")
      If Space = 0 Then
        WrapText = WrapText & Left(Text, Maxes(Index)) & vbLf
        Text = Mid(Text, Maxes(Index) + 1)
      Else
        WrapText = WrapText & Left(TextMax, Space - 1) & vbLf
        Text = Mid(Text, Space + 1)
      End If
    End If
    Index = (Index + 1) Mod 2
  Loop
  Lines = Split(WrapText & Text, vbLf)
  CellRef.Offset(1).Resize(UBound(Lines) + 1) = WorksheetFunction.Transpose(Lines)
End Sub

This subrountine is meant to be called from other code (a macro for example) and takes 3 arguments... first argument - a reference to the cell containing the text; second argument - the first, third, etc. line's maximum length; third argument - second, fourth, etc. line's maximum length. So, if the text you want to split was in cell D2, your code (macro) would call the above subroutine like this...

Sub Test()
  WrapTextAlternating Range("D1"), 30, 50
End Sub

The output from the subroutine will start at the cell immediately below the cell passed into the first argument and every individual line will be placed downward from there row-by-row.

 

[ukmike007]

Hi Rick

That is producing the result I want in that it populates different rows - is it possible to turn the Macro into a function so that it can be replicated at different places in the spreadsheet without re-writing the macro or relying on people running the macro?

Thanks

Mike

 

[Rick Rothstein]

That is producing the result I want in that it populates different rows - is it possible to turn the Macro into a function so that it can be replicated at different places in the spreadsheet without re-writing the macro or relying on people running the macro?

The problem with a function is that it returns a single value to a cell and you need the result distributed down multiple rows. I could create a function that returns an array, but that has two possible problem areas for you. First, you must select the number of cells to apply the function to before you enter the formula that calls the function into the formula bar... if you select too few cells, the function won't print out all of the split apart text; and you cannot simply extend the formula by copying a cell down, you would have to select the entire new range and then reenter the formula that calls the function into the formula bar. You could get around this problem by just selecting an abnormally large range of cells to apply the formula to (a little inefficient because array formulas are not the most efficient things around, but doable). Second, the formula must be committed using CTRL+SHIFT+ENTER (not just Enter by itself) which should not be a problem if you are doing the entry, but a possible problem if your users are doing so (especially if they need to extend the range as pointed out in the above text). Now, I could modify the macro to ask the user to pick the cell with the text and then pick the cell to output the results to (still a row-by-row output downward from that selected cell), of course, that would require your users to run the macro... would that be acceptable?

 

[peetrrr]

The problem with a function is that it returns a single value to a cell and you need the result distributed down multiple rows. I could create a function that returns an array, but that has two possible problem areas for you. First, you must select the number of cells to apply the function to before you enter the formula that calls the function into the formula bar... if you select too few cells, the function won't print out all of the split apart text; and you cannot simply extend the formula by copying a cell down, you would have to select the entire new range and then reenter the formula that calls the function into the formula bar. You could get around this problem by just selecting an abnormally large range of cells to apply the formula to (a little inefficient because array formulas are not the most efficient things around, but doable). Second, the formula must be committed using CTRL+SHIFT+ENTER (not just Enter by itself) which should not be a problem if you are doing the entry, but a possible problem if your users are doing so (especially if they need to extend the range as pointed out in the above text). Now, I could modify the macro to ask the user to pick the cell with the text and then pick the cell to output the results to (still a row-by-row output downward from that selected cell), of course, that would require your users to run the macro... would that be acceptable?

Hi there ,

I am new to this forum, and am needing a little help in applying the formula/ code to my vba and getting it to work.

I have a similar problem whereby I need to split a string of text up to a maximum of 43 characters to enter product data into our database, however a simple 'text to columns' function leaves me with 1/2 split words at the point of the 43rd character and this doesn't appear correctly on the documents sent to customers from the database. I need to split the text string by the 43rd character or the nearest space to that point to retain the full words, then continue to move the text into the cell, and so on.
I have tried to create a formula for this but the mind boggles a little with this and cant seem to get it to function properly.

Can you PLEEEAASE help with this and explain what the best method of splitting the text string up is for this.. and how I can apply this to my spread sheet. (I have thousands of products to do this for and don't want to have do them all manually!)

I shall look forward to hearing any replies and tips you may be able to offer,


Thank you in advance