Split large text in a cell upto a specified number of characters (conditions apply)

[jhamnani]

Hello,
Can somebody please help me in finding out the answer of the below problem :

I have a large text in one cell (2000 to 5000 characters). There are small sentences in this text each separated by a comma (,). I want to split this text in 1000 characters in each separate cell (Example in Screenshot below).
I can do that by using Left and Mid function which I did and it works fine but I don't want the sentences to break. These formulas will give me exact 1000 characters which could be anywhere within the sentence but I want the result to end before the comma or 1000th character, which ever is earlier.

The result should follow the below:
A - Characters not exceeding 1000 in any cell.
B - Sentence should not break and the result should end before the last comma or 1000th character, which ever is earlier.
C- The next cell should pick up where the previous cell ended but starting should not be with comma (').

If anyone having any idea of how to sort this I would be highly grateful. Thanks


-- removed inline image ---

 

[Perpa]

Hello,
Can somebody please help me in finding out the answer of the below problem :

I have a large text in one cell (2000 to 5000 characters). There are small sentences in this text each separated by a comma (,). I want to split this text in 1000 characters in each separate cell (Example in Screenshot below).
I can do that by using Left and Mid function which I did and it works fine but I don't want the sentences to break. These formulas will give me exact 1000 characters which could be anywhere within the sentence but I want the result to end before the comma or 1000th character, which ever is earlier.

The result should follow the below:
A - Characters not exceeding 1000 in any cell.
B - Sentence should not break and the result should end before the last comma or 1000th character, which ever is earlier.
C- The next cell should pick up where the previous cell ended but starting should not be with comma (').

jhamnani,
The image you tried to post did not post correctly.
To post an image use imgur.com or photobucket.com or other file sharing service. After you upload your image their site, they provide a link which you then place between the brackets [IMG)YourLink[/IMG].
To post a screen shot of your data use html Maker or Excel Jeanie. I use html Maker and it places the screen shot on the clipboard and then you just paste it into your post...very easy to use.

Below is some code I received from Peter_SSs on this forum. Perhaps it will get you going in the right direction. However it does not find the ','. Here is the link to that thread:
Separate Tex
Good Luck!
Perpa

Sub BreakItUp()
  Dim s As String
  Dim k As Long
  Dim result
  
  Const CharsPerLine As Long = 125     '<-Change to suit
  
  s = ActiveCell.Text
  ReDim result(1 To Len(s), 1 To 1)
  Do Until Len(s) = 0
    k = k + 1
    result(k, 1) = RTrim(Left(s, InStrRev(s & Space(CharsPerLine), " ", CharsPerLine + 1) - 1))
    s = Mid(s, Len(result(k, 1)) + 2)
  Loop
  ActiveCell.Offset(1).Resize(k).Value = result

End Sub