Break up info in 1 cell to multiple cells on another worksheet

[Andrewryan3]

I need to break up/spread out the information from cell J in worksheet #1 to cells CS through CX on worksheet #2. The information in cell J is similar to the following:

'For resolution unit 6/5/17 regarding activation fees for video group plus, Brandon PLUS negotiated savings $24,272.66 and fee billing is 21% below market and fee schedule savings'

Each cell in CS-CX can hold up to 100 characters (including spaces). The information in 'J' varies at any given time, so I am clueless where to even start

Thanks

 

[Rick Rothstein]

Your description is not very precise, so assuming Worksheet #1 is named "Sheet1" and Worksheet #2 is named "Sheet2" and that the first piece of text is in cell J1 on Sheet1 and you want the output to Sheet2 starting at cell CS1, then give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub WrapTextOnSpacesWithMaxCharactersPerLine()
  Dim Text As String, TextMax As String, SplitText As String
  Dim Space As Long, MaxChars As Long, CellWithText As Range
  MaxChars = 100
  For Each CellWithText In Sheets("Sheet1").Range("J1", Sheets("Sheet1").Cells(Rows.Count, "J").End(xlUp))
    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
    Sheets("Sheet2").Cells(CellWithText.Row, "CS").Value = SplitText & Text
  Next
  Sheets("Sheet2").Columns("CS").TextToColumns , xlDelimited, , , False, False, False, False, True, vbLf
End Sub[/td]
[/tr]
[/table]

 

[JLGWhiz]

a good place to start is to define if you only have 1 row of data or if you have more than one row and what is the row number. Then tell us how you want your text distributed among the seven cells in the second worksheet. ie. Five words per cell or groups of words determined by dividing the source range content by seven or by using keywords or numbers. If is very difficult to make any code work without knowking what the results should look like. A before and after example is always helpful.

 

[Roderick_E]

I need to break up/spread out the information from cell J in worksheet #1 to cells CS through CX on worksheet #2. The information in cell J is similar to the following:

'For resolution unit 6/5/17 regarding activation fees for video group plus, Brandon PLUS negotiated savings $24,272.66 and fee billing is 21% below market and fee schedule savings'

Each cell in CS-CX can hold up to 100 characters (including spaces). The information in 'J' varies at any given time, so I am clueless where to even start

Thanks

So the original data in column J is of varying lengths and you simply want to break it up over 6 columns? Do you want this evenly and is there a limit how much each column CS-CX can hold?

 

[Andrewryan3]

Your description is not very precise, so assuming Worksheet #1 is named "Sheet1" and Worksheet #2 is named "Sheet2" and that the first piece of text is in cell J1 on Sheet1 and you want the output to Sheet2 starting at cell CS1, then give this macro a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD] 
[/TD]
[/TR]
</tbody>[/TABLE]

Rick, this leaves blank cells in between some of the text. for example: CS is populated and CT is blank and the 90% rest of the information is in CU , CV blank and 1 word in CW

 

[Andrewryan3]

a good place to start is to define if you only have 1 row of data or if you have more than one row and what is the row number. Then tell us how you want your text distributed among the seven cells in the second worksheet. ie. Five words per cell or groups of words determined by dividing the source range content by seven or by using keywords or numbers. If is very difficult to make any code work without knowking what the results should look like. A before and after example is always helpful.

JLGWhis,
I have almost 3000 rows of information. Rows 2-3001. each of the cells in the second worksheet can hold up to 100 characters (because it needs to be uploaded, so say 40 words per cell) The amount of information can vary from the parent cell on a month to month basis. I am simply looking to keep words together in the cell and not chop them up. When this is uploaded , it will print out from our program on a receipt

 

[Andrewryan3]

So the original data in column J is of varying lengths and you simply want to break it up over 6 columns? Do you want this evenly and is there a limit how much each column CS-CX can hold?

Roderick, the information does vary, yes. I simply need whole words in each of the columns and it can be up to 100.

 

[Roderick_E]

Roderick, the information does vary, yes. I simply need whole words in each of the columns and it can be up to 100.

So, if J had only 98 characters including spaces and punctuation, we could put it all into CS and leave the other columns blank? You could test length of string and if greater than 100 then break at commas.

 

[Rick Rothstein]

Rick, this leaves blank cells in between some of the text. for example: CS is populated and CT is blank and the 90% rest of the information is in CU , CV blank and 1 word in CW

Two possibilities... one, your text has Line Feeds in it or, two, it has multiple adjacent spaces in it. This modification to my code should handle these problems... let me know if it works or not.

[table="width: 500"]
[tr]
	[td]Sub WrapTextOnSpacesWithMaxCharactersPerLine()
  Dim Text As String, TextMax As String, SplitText As String
  Dim Space As Long, MaxChars As Long, CellWithText As Range
  MaxChars = 100
  For Each CellWithText In Sheets("Sheet1").Range("J1", Sheets("Sheet1").Cells(Rows.Count, "J").End(xlUp))
    Text = Application.Trim(Replace(CellWithText.Value, vbLf, " "))
    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
    Sheets("Sheet2").Cells(CellWithText.Row, "CS").Value = SplitText & Text
  Next
  Sheets("Sheet2").Columns("CS").TextToColumns , xlDelimited, , True, False, False, False, False, True, vbLf
  Sheets("Sheet2").UsedRange.SpecialCells(xlBlanks).Delete xlShiftToLeft
End Sub[/td]
[/tr]
[/table]

 

[Andrewryan3]

So, if J had only 98 characters including spaces and punctuation, we could put it all into CS and leave the other columns blank? You could test length of string and if greater than 100 then break at commas.

Yes, and no. This will print out on a receipt... So if this explanation goes across the whole sheet it will look sloppy, but if we were to split it into say 45 and 53, well that would look more aesthetic on the receipt.

when we upload this into our software, the representing columns 'CS-CX' will print the information stacked on the bottom of the receipt. So if they are somewhat evenly spaced, it would be more pleasing to the "boss"