Split text after every last whole word every 30 characters

[user47]

Good day all,

I have address data that is unfortunately punctuated with line feed characters.

I need to split the data at every 30 characters.... but on the condition that the 30th character cannot be in the middle of a word. If it is, then only the last whole word before the 30th position can stay and the next word after that gets spilled over into the next column for the next 30 characters... on the condition that the 60th character cannot be in the middle of a word etc and so on. Is there any way to do this in excel? I have prepared an example below (manually) as best as I can but please feel free to let me know if any clarifications are needed as I know this may be a tricky one to word correctly for help.

Thanking you all in advance.

WorkBook1.xlsx
	A	B	C	D	E	F
1		30 characters max per column rounded down to last whole word
2	Address	each col has whole words only that fit under 30 characters per col
3	74850 Brightly Blazing Glory Days Long Road Candyland Avenue Four Interdimensional Galaxy Rift Sector Seventy Seven Point Six	74850 Brightly Blazing Glory	Days Road Candyland Avenue	Four Interdimensional Galaxy	Rift Sector Seventy Seven	Point Six
4	Length of string	28	26	28	25
Sheet2

Cell Formulas
Range	Formula
B4:E4	B4	=LEN(B3)

 

[Eric W]

There's probably a cleverer way, but maybe:

Book1
	A	B	C	D	E	F	G	H
1			30 characters max per column rounded down to last whole word
2	Address		each col has whole words only that fit under 30 characters per col
3	74850 Brightly Blazing Glory Days Long Road Candyland Avenue Four Interdimensional Galaxy Rift Sector Seventy Seven Point Six		4850 Brightly Blazing Glory	Days Long Road Candyland	Avenue Four Interdimensional	Galaxy Rift Sector Seventy	Seven Point Six
4	Length of string		27	24	28	26	15	0
Sheet1

Cell Formulas
Range	Formula
C3:H3	C3	=LET(a,TRIM(SUBSTITUTE($A3,CHAR(10)," ")),b,MID(a,LEN(TEXTJOIN(" ",,$B3:B3))+2,LEN(a))&" ",s,SEQUENCE(30),TRIM(LEFT(b,MAX(IF(MID(b,s,1)=" ",s)))))
C4:H4	C4	=LEN(C3)

 

[user47]

There's probably a cleverer way, but maybe:

Book1
	A	B	C	D	E	F	G	H
1			30 characters max per column rounded down to last whole word
2	Address		each col has whole words only that fit under 30 characters per col
3	74850 Brightly Blazing Glory Days Long Road Candyland Avenue Four Interdimensional Galaxy Rift Sector Seventy Seven Point Six		4850 Brightly Blazing Glory	Days Long Road Candyland	Avenue Four Interdimensional	Galaxy Rift Sector Seventy	Seven Point Six
4	Length of string		27	24	28	26	15	0
Sheet1

Cell Formulas
Range	Formula
C3:H3	C3	=LET(a,TRIM(SUBSTITUTE($A3,CHAR(10)," ")),b,MID(a,LEN(TEXTJOIN(" ",,$B3:B3))+2,LEN(a))&" ",s,SEQUENCE(30),TRIM(LEFT(b,MAX(IF(MID(b,s,1)=" ",s)))))
C4:H4	C4	=LEN(C3)

Hello, good day Sir,

Thank you! You are amazing!!!!

This worked although when i tried it for the columns I needed it for, it missed the first character in the first column. I am still wrapping my tiny mind around how the formula works but i was able to use left() to fix the specific issue where it left out the first character. I suspect it has to do with maybe which column I am entering it relative to where the data is etc. No worries though, I can gladly use that fix as I try to wrap my novice mind around the glorious wit in your solution. Thanks again so much!

 

[Tom.Jones]

This worked although when i tried it for the columns I needed it for, it missed the first character in the first column. I am still wrapping my tiny mind around how the formula works but i was able to use left() to fix the specific issue where it left out the first character. I suspect it has to do with maybe which column I am entering it relative to where the data is etc. No worries though, I can gladly use that fix as I try to wrap my novice mind around the glorious wit in your solution. Thanks again so much!

Small modification to Erik's formula:

=LET(a,TRIM(SUBSTITUTE($A3,CHAR(10)," ")),b,MID(a,LEN(TEXTJOIN(" ",,$B3:B3))+1,LEN(a))&" ",s,SEQUENCE(30),TRIM(LEFT(b,MAX(IF(MID(b,s,1)=" ",s)))))

 

[user47]

Small modification to Erik's formula:

=LET(a,TRIM(SUBSTITUTE($A3,CHAR(10)," ")),b,MID(a,LEN(TEXTJOIN(" ",,$B3:B3))+1,LEN(a))&" ",s,SEQUENCE(30),TRIM(LEFT(b,MAX(IF(MID(b,s,1)=" ",s)))))

Whoa! Thank you! That fixed it when applying it to my data set!

(Just to be clear Erik's solution worked fine with the example I initially gave but just with my dataset it was off by 1 character. Your modification works perfectly with the dataset.)

Thank you both!

 

[Eric W]

Glad we could help! Tom, thanks for the assist.