Split large text in a cell

[jhamnani]

Hello,

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.
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

 

[gallen]

If you are ok with using VBA it's simpler. I'm personally not proficient enough with Functions to achieve it but VBA has an InStrRev function which counts backwards. I could achieve what you need in VBA

 

[Comfy]

This should work although not very elegant:

Option Explicit


Function SplitSentence(sentence As String, i As Long)


Dim s As Variant
Dim j As Long
Dim temp As String
Dim ReturnArr() As Variant
Dim sCount As Long


s = Split(sentence, ",")
sCount = 0




For j = LBound(s) To UBound(s)
ReDim Preserve ReturnArr(sCount)
If Len(temp) + Len(s(j) + ",") <= 1000 Then
    temp = temp + s(j) + ","
    ReturnArr(sCount) = temp
Else
sCount = sCount + 1
temp = ""
j = j - 1
End If
Next j


If i = UBound(ReturnArr) Then
SplitSentence = Left(ReturnArr(i), Len(ReturnArr(i)) - 1)
Else
SplitSentence = ReturnArr(i)
End If
End Function

Excel 2010
	A	B	C
1	text	length
2	This is a sentence ,Th	4060
3
4
5	This is a sentence ,This is	812	This formula would be =SplitSentence($A$2,0)
6	This is a sentence ,This is	812	This formula would be =SplitSentence($A$2,1)
7	This is a sentence ,This is	812	This formula would be =SplitSentence($A$2,2)
8	This is a sentence ,This is	812	etc
9	This is a sentence ,This is	812	etc
10	#VALUE!	#VALUE!
11		4060
Sheet1

Truncated the data in the cells for posting to the forum.

 

[jhamnani]

I know that this was supposed to be done by VBA only but I am also novice to this that is why I had to ask for help. Anyway, I appreciate your help.