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.
|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.
Press Enter key, then drag fill handle down to D6.
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.
- 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.
- 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.
Return the specific characters from the middle of text string.