Excel Formula: Trim text to n words
This tutorial introduces a formula which combine LEFT, FIND and SUBSTITUTE functions to extract first n words from a long string in Excel cell, and also explains how to use the formula.
Generic formula:
| =LEFT(txt,FIND("~",SUBSTITUTE(txt," ","~",n))-1) |
Arguments
| txt: the text string or cell you want to trim. |
| N: the number of words you want to extract from left side of the given text string. |
How this formula work
Take an instance, you want to extract part of words from left side in each cell of range B3:B6 to D3:D6, the number of words decided by the value in C3:C6. Please use below formula in D3.
| =LEFT(B3,FIND("~",SUBSTITUTE(B3," ","~",C3))-1) |
Press Enter key, then drag fill handle down to D6.
Explanation
SUBSTITUTE function: this function replaces a specific text with another one. Here, using this formula SUBSTITUTE(B3," ","~",C3) to replace space “ “ to “~” in the nth position.
FIND function: to get the location of a specific text. Here FIND("~",SUBSTITUTE(B3," ","~",C3) finds the location of “~” in B3.
LEFT function: this function is used to extract text from left side of a given text string. Here the formula LEFT(B3,FIND("~",SUBSTITUTE(B3," ","~",C3))-1) can be seen as LEFT(B3,54-1) which will extract 53 characters from left side of the text in cell B3.
Sample File
Click to download sample fileRelative Formulas
- Convert Letter To Number
This tutorial provides some formulas for solving the problems on translating letters to numbers in Excel. - Remove From Right Of Text
Here introduces the formula to remove characters from right side of a text string in Excel. - Extract The Last Word From Text String In Excel
This tutorial provides a formula to extract last word from the give text string. - Extract The First Word From Text String In Excel
This tutorial provides a formula to extract first word from the give text string.
Relative Functions
- SUBSTITUTE function
Replace a text in a specific position with another one. - FIND function
Find the location of a character in the text string. - LEFT function
Extract part of text from left side. - MID
Return the specific characters from the middle of text string.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
