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

 

[Rick Rothstein]

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!)

Did you notice the link to my mini-blog article that someone had posted earlier in this thread?

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

It split text up into multiple lines of text within the same cell in the way you described you want. What I am unsure of from your question above is how you want that spit apart text to be shown in the worksheet... like the code does, each cell's text placed split apart in the a single cell or split apart into separate cells? If you tell us where your text is now (what range) and where you want the split apart results to go... same cell containing the existing text, cell next to the cell containing the existing text or in multiple cells somewhere else in the workbook (where)... I will be happy to modify that mini-blog article code for you.

 

[peetrrr]

Did you notice the link to my mini-blog article that someone had posted earlier in this thread?

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

It split text up into multiple lines of text within the same cell in the way you described you want. What I am unsure of from your question above is how you want that spit apart text to be shown in the worksheet... like the code does, each cell's text placed split apart in the a single cell or split apart into separate cells? If you tell us where your text is now (what range) and where you want the split apart results to go... same cell containing the existing text, cell next to the cell containing the existing text or in multiple cells somewhere else in the workbook (where)... I will be happy to modify that mini-blog article code for you.

Hi Rick,

I did see the formula you posted previously but as I am a novice when it comes to writing macros and using vba I could not get this code to work. All i did to try this though was open vba in excel and clicked to view code. In here I just pasted what you had posted and clicked the run macro button. It didnt do anything, and I know I have not done this correctly.

I do want to split a cell which contains various lengths of text into separate lines (Some lines have 50 characters some have 100). This is to import the data into description lines in our product database.

The description lines only allow a maximum of 43 characters in each line so I will need to split at the 43rd character (Or the nearest space to that to keep the words complete). I can split using the text to columns function, however the problem occurs when I do this as it doesnt consider that the words are broken when doing so and outputs 'broken' words e.g:

This product comes free wit
h an extra pair of hands to hel
p you get the job done


If you can explain how I will be able to add the code you posted into my vba, I would be grateful.

(FYI the text is in cell M2 with the spreadsheet I am working on and split text can be put into columns to the right of that to a maximum of 3 extra columns)

I hope I have explained my problem further and thank you for your reply Rick.

 

[Rick Rothstein]

If you can explain how I will be able to add the code you posted into my vba, I would be grateful.

(FYI the text is in cell M2 with the spreadsheet I am working on and split text can be put into columns to the right of that to a maximum of 3 extra columns)

Okay, I modified my mini-blog article's macro code for you... put the following code in a standard module (Insert/Module on the VB editor's menu bar) and then run it. It will ask you to select the cell or cells (in a column only) that you want to perform this action on (so if you want only cells M2, select it and click OK on the dialog box; if you wanted more cells, say M2:M50, then select all of them and then click the OK button)...

Sub WrapTextOnSpacesWithMaxCharactersPerLine()
  Dim Text As String, TextMax As String, SplitText As String
  Dim Space As Long, MaxChars As Long, OS As Long
  Dim Source As Range, CellWithText As Range
  Const DestinationOffset As Long = 1
  MaxChars = 43
  On Error GoTo NoCellsSelected
  Set Source = Application.InputBox("Select cells to process:", Type:=8)
  On Error GoTo 0
  For Each CellWithText In Source
    Text = CellWithText.Value
    SplitText = ""
    Do While Len(Text) > MaxChars
      TextMax = Left(Text, MaxChars + 1)
      If Right(TextMax, 1) = " " Then
        SplitText = SplitText & RTrim(TextMax) & vbLf
        Text = Mid(Text, MaxChars + 2)
      Else
        Space = InStrRev(TextMax, " ")
        If Space = 0 Then
          SplitText = SplitText & Left(Text, MaxChars) & vbLf
          Text = Mid(Text, MaxChars + 1)
        Else
          SplitText = SplitText & Left(TextMax, Space - 1) & vbLf
          Text = Mid(Text, Space + 1)
        End If
      End If
    Loop
    With CellWithText.Offset(, DestinationOffset)
      .Value = SplitText & Text
      .TextToColumns .Cells, xlDelimited, , True, False, False, False, False, True, vbLf
      OS = 0
      Do While Len(.Offset(, OS).Value)
        .Offset(, OS).WrapText = False
        .Offset(, OS).EntireColumn.AutoFit
        OS = OS + 1
      Loop
    End With
  Next
  Exit Sub
NoCellsSelected:
End Sub

 

[peetrrr]

Okay, I modified my mini-blog article's macro code for you... put the following code in a standard module (Insert/Module on the VB editor's menu bar) and then run it. It will ask you to select the cell or cells (in a column only) that you want to perform this action on (so if you want only cells M2, select it and click OK on the dialog box; if you wanted more cells, say M2:M50, then select all of them and then click the OK button)...

Rick this has worked perfectly!!

I was not sure of how to insert the VBA code and now I do I have entered into the spread sheet and breaks the text up just as I need it!

I am very grateful for your help with this and would definately recommend your site MeExcel to anyone with any issues as I have found a couple of other pages that have helped in other areas on your site too.

Thanks again & enjoy the rest of your day