Truncate text in a cell after so many words or characters

[theblade24]

Hi,

I'm looking for a formula to cut off a text string at either a word limit or perhaps at the end of the closest word to a cutoff value.

"Mary went to the store to shop"

If that is in a cell, what formula in another cell would shorten that after 5 words? "Mary went to the store"

If that is in a cell, what formula would cut it off at the end of the closest word under 20 characters? In this case "Mary went to the"

If that is in a cell, what formula would cut it off at the end of the closest word over 20 characters? In this case "Mary went to the store"

Thank you in advance!

[daddylonglegs]

For first 5 words try this formula

=LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",5))-1)

at least 20 characters with full words...

=LEFT(A1,FIND(" ",A1,21))

.......and at most 20 characters with full words

=LEFT(A1,LOOKUP(21,FIND(" ",A1,ROW(INDIRECT("1:21"))))-1)

[theblade24]

Right on! Those two work perfect, thanks!

You missed one however

If that is in a cell, what formula would cut it off at the end of the closest word under 20 characters? In this case "Mary went to the"

[daddylonglegs]

I was a bit slow with that one, I added it to my original post.....

[theblade24]

See, this is why I come here!

Quick, and done correctly!!

Thank you so much!

[theblade24]

Alright, I'm revisiting truncating text after so many characters at the end of the last full word

=LEFT(A1,LOOKUP(21,FIND(" ",A1,ROW(INDIRECT("1:21"))))-1)

For some reason this only seems to work up to around 50 characters.

If I set =LEFT(A1,LOOKUP(51,FIND(" ",A1,ROW(INDIRECT("1:51"))))-1) it truncates my 90 character sentence at 48 where the closest word ends.

If I set it higher =LEFT(A1,LOOKUP(71,FIND(" ",A1,ROW(INDIRECT("1:71"))))-1) it no longer works for some reason. It still truncates in the 50 char range.

Is there a reason this would only work up to a certain length? Is there anything that needs to be changed to work on longer strings?

[daddylonglegs]

I don't think there's a problem with the formula - I would expect it to work with any number - I just tried that version with 71 and it truncated the text to 65 chars which is what I expected - can you give a specific example of the text where it didn't work?

[theblade24]

Bobby Allison Autographed Racing 8X10 Photo Black Custom Frame (64 characters)

=LEFT(AR17,LOOKUP(91,FIND(" ",AR17,ROW(INDIRECT("1:91"))))-1)

Theoretically, shouldn't 91 return the whole string?

Right now it is cut at Bobby Allison Autographed Racing 8X10 Photo Black Custom (58 characters)

[daddylonglegs]

Yes, you're right - you lose the last word instead of getting the full text when the full text is smaller than the specified number - you can use this modified version to fix that

=LEFT(AR17,LOOKUP(91,FIND(" ",AR17&" ",ROW(INDIRECT("1:91"))))-1)

[theblade24]

Ok, one more step daddylonglegs

You gave me this to truncate as you know =LEFT(AI2,LOOKUP(91,FIND(" ",AI2&" ",ROW(INDIRECT("1:91"))))-1)

Once the sentence has been truncated by your formula, on the truncated result I need to perform this

=SUBSTITUTE(TRIM(LOWER(NONALPHADASH(AI2)&"-"&B2)),"--","-") Don't ask me what it does It has vba in it. Just bear with me here......

Instead of using those two formulas in 2 different cells to get the end result. How can I merge it so it does your truncation and then the other formula?

I've tried =(LEFT(AI2,LOOKUP(91,FIND(" ",AI2&" ",ROW(INDIRECT("1:91"))))-1)SUBSTITUTE(TRIM(LOWER(NONALPHADASH(AI2)&"-"&B2)),"--","-"))

And it won't accept the whole formula. I basically need your result to then have the other formula run on it to give a final final result.

Ideas?