Quick, clean, and to the point

Trim text to n words

Excel formula: Trim text to n words
Generic formula 
=LEFT(txt,FIND("#",SUBSTITUTE(txt," ","#",n))-1)

To trim text to a certain number of words, you can use a formula based on the SUBSTITUTE, FIND, and LEFT functions. In the example shown, the formula in xxx is:

=LEFT(B5,FIND("#",SUBSTITUTE(B5," ","#",C5))-1)

We need a way to split text at a certain marker that corresponds to a certain number of words. Excel doesn't have a built-in function to parse text by word, so are using the SUBSTITUTE function's "instance" argument to replace an "nth space" character with the pound sign (#), then using FIND and LEFT to discard all text after the marker.

Working from the inside out, SUBSTITUTE is configured to replace the nth occurence of a space character, where n comes from column C, the text comes from column B, and "#" is hardcoded.

=SUBSTITUTE(B5," ","#",C5)
=SUBSTITUTE("The cat sat on the mat."," ","#",3)
="The cat sat#on the mat."

The resulting string is returned to the FIND function, configured to look for "#".

=FIND("#","The cat sat#on the mat.)

Since the "#" is the 12th character in the text, FIND returns 12. We don't want to include the space character itself in, so we subtract 1:


LEFT returns the final result from the formula, "The cat sat".

Note: the pound character ("#") is arbitrary and can be replaced with any character that won't appear in the text.

Add elipses or other character

To add "..." to the end of the trimmed text, use concatenation like this:

=LEFT(B5,FIND("#",SUBSTITUTE(B5," ","#",C5))-1)&"..."

You can replace "..." with anything you like.

Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Charts video training course
Video training for Excel Tables
Dynamic Array Formulas