split cell at specific character count but break at a whole word

[ESACAWIP]

Hoping someone can help me out, having a lot of trouble figuring out how to do this.


I have a large amount of text in a single cell

Example: (Cell A1)

All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.

I need to break up the text into smaller chucks. Each chuck can only be 40 characters long and has to end on a whole word.

Output example:

Output	Character Count (len)
All investing is subject to risk, (Cell A2)	33
including the possible loss of the money (Cell A3)	40
you invest. (Cell A4)	11

This is just an example. I actually have cells that have 40,000 character count in a single cell and I need to break it up into 10,000 character count chucks.


Please let me know if this is possible. Thank you so much!

 

[RobP]

Hi,

This code should split your cell A1 up, and place the subtext in the rows below it starting row 3. NOTE: max string allowed is 32768 characters

Hope it helps - you can change the location accordingly.

Rgds
Rob

Sub text_splitter()

Dim full_text, sub_text As String
Dim row_num, text_len, start_pos, end_pos As Long

full_text = Range("A1")  'note Excel allows String sizes to 32768 chars only.
text_len = Len(full_text)

row_num = 3 '(first row to add text to)
start_pos = 1

Do While start_pos < text_len

    If start_pos + 40 > text_len Then 'check for end of text
        end_pos = text_len
    Else
        end_pos = InStrRev(full_text, " ", start_pos + 40)
    End If
    sub_text = Mid(full_text, start_pos, end_pos - start_pos)
    Range("A" & row_num) = sub_text
    row_num = row_num + 1
    start_pos = end_pos + 1

Loop

End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.
2
3	All investing is subject to risk,
4	including the possible loss of the money
5	you invest. All investing is subject to
6	risk, including the possible loss of the
7	money you invest
8
9
10	173
11
12	33
13	40
14	39
15	40
16	16
17
Sheet1

Cell Formulas
Range	Formula
A10,A12:A16	A10	=LEN(A1)

 

[Fuji]

Here's how I read your problem

Sub test()
    Dim r As Range, s$, i&
    Const n& = 40
    With CreateObject("VBScript.RegExp")
        .Global = True
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If r <> "" Then
                .Pattern = "([!?,.])"
                s = Join(WorksheetFunction.Unique(Application.Trim(Split(.Replace(r, "$1" & Chr(2)), Chr(2))), True))
                .Pattern = "\b(.{1," & n & "}\b)"
                For i = 0 To .Execute(s).Count - 1
                    r(, i + 3) = .Execute(s)(i).submatches(0)
                Next
            End If
        Next
    End With
End Sub

Result after macro.

Book1
	A	B	C	D	E
1	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.		All investing is subject to risk,	including the possible loss of the money	you invest
Sheet1

 

[felixstraube]

Here is a formula alternative. Max characters is 32766. Which is the maximum character of a cell (32767 i think).

You have to copy the formula in F till ALP.

Book5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Length	Equal?	Textjoined parts	1	2	3	4	5	6	7	8	9	10
2	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	173	TRUE	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	All investing is subject to risk,	including the possible loss of the money	you invest. All investing is subject to	risk, including the possible loss of the	money you invest.
Sheet8

Cell Formulas
Range	Formula
E1:ALP1	E1	=SEQUENCE(, 1000)
B2	B2	=LEN(A2)
C2	C2	=A2=D2
D2	D2	=TEXTJOIN(" ", , E2:XFD2)
E2	E2	=LET( t, LEFT($A2, 41), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,41, -1)), 0)), TRIM(LEFT(t, max)) )
F2:N2	F2	=IFERROR(LET(nt, TRIM(RIGHT($A2, LEN($A2)-SUM(LEN($E2:E2&" "))+1)), t, LEFT(nt, 41), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,41, -1)), 0)), IF(LEN(nt)>40, TRIM(LEFT(t, max)), nt) ), "")
Dynamic array formulas.

I tested this:

 

[ESACAWIP]

Here's how I read your problem

Sub test()
    Dim r As Range, s$, i&
    Const n& = 40
    With CreateObject("VBScript.RegExp")
        .Global = True
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If r <> "" Then
                .Pattern = "([!?,.])"
                s = Join(WorksheetFunction.Unique(Application.Trim(Split(.Replace(r, "$1" & Chr(2)), Chr(2))), True))
                .Pattern = "\b(.{1," & n & "}\b)"
                For i = 0 To .Execute(s).Count - 1
                    r(, i + 3) = .Execute(s)(i).submatches(0)
                Next
            End If
        Next
    End With
End Sub

Result after macro.

Book1
	A	B	C	D	E
1	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.		All investing is subject to risk,	including the possible loss of the money	you invest
Sheet1

Here is a formula alternative. Max characters is 32766. Which is the maximum character of a cell (32767 i think).

You have to copy the formula in F till ALP.

Book5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Length	Equal?	Textjoined parts	1	2	3	4	5	6	7	8	9	10
2	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	173	TRUE	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	All investing is subject to risk,	including the possible loss of the money	you invest. All investing is subject to	risk, including the possible loss of the	money you invest.
Sheet8

Cell Formulas
Range	Formula
E1:ALP1	E1	=SEQUENCE(, 1000)
B2	B2	=LEN(A2)
C2	C2	=A2=D2
D2	D2	=TEXTJOIN(" ", , E2:XFD2)
E2	E2	=LET( t, LEFT($A2, 41), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,41, -1)), 0)), TRIM(LEFT(t, max)) )
F2:N2	F2	=IFERROR(LET(nt, TRIM(RIGHT($A2, LEN($A2)-SUM(LEN($E2:E2&" "))+1)), t, LEFT(nt, 41), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,41, -1)), 0)), IF(LEN(nt)>40, TRIM(LEFT(t, max)), nt) ), "")
Dynamic array formulas.

I tested this:

View attachment 114715

I've tried both these solutions and they work great with smaller text, but I tried changing the vba / formulas so that instead of a split at 40 characters, the original text is 30,000 characters and I would like to split at every 10,000 characters.

However, I think i did something wrong because its not working like it does with smaller text. Maybe I edited it incorrectly?

 

[Fuji]

I've tried both these solutions and they work great with smaller text

Confusing.
My code works differently.

If you simply want to split the text by fixed length without removing duplicate sentence(s), then

Sub test()
    Dim r As Range, s$, x&, i&
    Const n& = 40
    For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
        s = r: i = 2
        Do While Len(s) > n
            x = InStrRev(s, " ", n + 1)
            i = i + 1
            r(, i) = Left$(s, x - 1)
            s = Trim$(Mid$(s, x + 1))
        Loop
        If Len(s) Then i = i + 1: r(, i) = s
    Next
End Sub

 

[felixstraube]

Here the formula to split into 10000 chars:

Book5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Length	Equal?	Textjoined parts	1	2	3	4	5	6	7	8	9	10
2	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	173	TRUE	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.	All investing is subject to risk, including the possible loss of the money you invest. All investing is subject to risk, including the possible loss of the money you invest.
SplitText

Cell Formulas
Range	Formula
E1:N1	E1	=SEQUENCE(, 10)
B2	B2	=LEN(A2)
C2	C2	=A2=D2
D2	D2	=TEXTJOIN(" ", , E2:XFD2)
E2	E2	=LET( t, LEFT($A2, 10001), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,10001, -1)), 0)), IF(LEN(t)>10000, TRIM(LEFT(t, max)), t) )
F2:N2	F2	=IFERROR(LET(nt, TRIM(RIGHT($A2, LEN($A2)-SUM(LEN($E2:E2&" "))+1)), t, LEFT(nt, 10001), max, MAX(IFERROR(FIND(" ", t, SEQUENCE(40,,10001, -1)), 0)), IF(LEN(nt)>10000, TRIM(LEFT(t, max)), nt) ), "")
Dynamic array formulas.