Truncate Whole Words within Max Length Set

[zero269]

Hello,

I'm looking for a way to truncate the length of data that I'm pulling from another Sheet using XLOOKUP.

VBA Testing.xlsm
	A	B	C	D
1	Quiz	Title	Data	LEN
2	176888	A Funny Thing Happened on the Way to School...	xxxxxxxxx	46
3	178125	A Pirate's Mother Goose (And Other Rhymes)	xxxxxxxxx	42
4	187860	Alexander Hamilton: From Orphan to Founding Father	xxxxxxxxx	50
5	126235	Back-to-School Fright From the Black Lagoon	xxxxxxxxx	43
6	167392	Every Planet Has a Place: A Book About Our Solar System	xxxxxxxxx	55
Truncate

Cell Formulas
Range	Formula
B2:B6	B2	=XLOOKUP([@Quiz],tbl_Books[Quiz],tbl_Books[Title],"")
D2:D6	D2	=LEN([@Title])

I tested out using the LEN Function to limit the number of characters to 48.

TEST using Left Function (Yellow table)

=LEFT(tbl_PrintLog[@Title],48)

It works OK, but I found the partial words distracting, so looked for a solution online to show whole words only.

VBA Testing.xlsm
	F	G	H
1	TEST using Left Function	Data	LEN
2	A Funny Thing Happened on the Way to School...	xxxxxxxxx	46
3	A Pirate's Mother Goose (And Other Rhymes)	xxxxxxxxx	42
4	Alexander Hamilton: From Orphan to Founding Fath	xxxxxxxxx	48
5	Back-to-School Fright From the Black Lagoon	xxxxxxxxx	43
6	Every Planet Has a Place: A Book About Our Solar	xxxxxxxxx	48
Truncate

Cell Formulas
Range	Formula
F2:F6	F2	=LEFT(tbl_PrintLog[@Title],48)
H2:H6	H2	=LEN(F2)

TRIM + TEXTBEFORE + LEFT (Blue table)

=TRIM(TEXTBEFORE(LEFT(tbl_PrintLog[@Title],48)," ",-1))

Unfortunately, this method is truncating too many words even though it's set to 48 max.

VBA Testing.xlsm
	J	K	L
1	TRIM + TEXTBEFORE + LEFT	Data	LEN
2	A Funny Thing Happened on the Way to	xxxxxxxxx	36
3	A Pirate's Mother Goose (And Other	xxxxxxxxx	34
4	Alexander Hamilton: From Orphan to Founding	xxxxxxxxx	43
5	Back-to-School Fright From the Black	xxxxxxxxx	36
6	Every Planet Has a Place: A Book About Our	xxxxxxxxx	42
Truncate

Cell Formulas
Range	Formula
J2:J6	J2	=TRIM(TEXTBEFORE(LEFT(tbl_PrintLog[@Title],48)," ",-1))
L2:L6	L2	=LEN(J2)

I was thinking perhaps I could count the number of words first, then somehow only grab the words that fall within a maximum length of 48 characters… and disregard any whole words that land on 49 and up.

The following formula counts the number of words (characters) separated by a single space.

=TRIM(TEXTBEFORE(LEFT(tbl_PrintLog[@Title],48)," ",-1))

The following GREEN Table are the desired results:

VBA Testing.xlsm
	N	O	P
1	Desired Output	Data	LEN
2	A Funny Thing Happened on the Way to School...	xxxxxxxxx	46
3	A Pirate's Mother Goose (And Other Rhymes)	xxxxxxxxx	42
4	Alexander Hamilton: From Orphan to Founding	xxxxxxxxx	43
5	Back-to-School Fright From the Black Lagoon	xxxxxxxxx	43
6	Every Planet Has a Place: A Book About Our Solar	xxxxxxxxx	48
Truncate

Cell Formulas
Range	Formula
P2:P6	P2	=LEN(N2)

Any assistance with this would be greatly appreciated.


Thanks…

 

[Peter_SSs]

Is this any use?

23 05 29.xlsm
	B
2	A Funny Thing Happened on the Way to School...
3	A Pirate's Mother Goose (And Other Rhymes)
4	Alexander Hamilton: From Orphan to Founding Father
5	Back-to-School Fright From the Black Lagoon
6	Every Planet Has a Place: A Book About Our Solar System
7
8	A Funny Thing Happened on the Way to School...
9	A Pirate's Mother Goose (And Other Rhymes)
10	Alexander Hamilton: From Orphan to Founding
11	Back-to-School Fright From the Black Lagoon
12	Every Planet Has a Place: A Book About Our Solar
Truncate

Cell Formulas
Range	Formula
B8:B12	B8	=LET(s,LEFT(B2,49),IF(LEN(s)<49,s,TEXTBEFORE(s," ",-1)))

 

[zero269]

Is this any use?

Hi Peter,

I'd say it's extremely useful.

To incorporate it with my xlookup's return value, I simply replaced the cell reference (B2) in your code with my xlookup formula; it worked like a charm.

VBA Testing.xlsm
	Z
2	A Funny Thing Happened on the Way to School...
3	A Pirate's Mother Goose (And Other Rhymes)
4	Alexander Hamilton: From Orphan to Founding
5	Back-to-School Fright From the Black Lagoon
6	Every Planet Has a Place: A Book About Our Solar
Truncate

Cell Formulas
Range	Formula
Z2:Z6	Z2	=LET(s,LEFT(XLOOKUP(tbl_PrintLog[@Quiz],tbl_Books[Quiz],tbl_Books[Title],""),49),IF(LEN(s)<49,s,TEXTBEFORE(s," ",-1)))

This will even save me a little bit on printer ink and leave the necessary room I need to make my notations at the end of the book Title.

Thank you again for repeatedly sharing your expertise with me. It's very much appreciated.

 

[Peter_SSs]

You're welcome. Glad it worked for you. Thanks for the follow-up.