How to extract all but the first/last word in Excel?
When working with text data in Excel, you may often need to remove either the first or the last word from a cell, keeping only the middle or remaining parts. This can be necessary in data cleaning, preparing lists for further analysis, or standardizing data imported from various sources. While manually copying and pasting desired words is possible, it quickly becomes impractical and error-prone when dealing with a large number of cells. Therefore, finding an efficient approach to extract all words except the first or last greatly improves your productivity and data accuracy.
Below are two commonly used solutions in Excel:
Extract all but the first/last word with formulas
Extract all but the first/last word with Kutools for Excel
Extract all but the first/last word with formulas
Excel formulas provide a straightforward way to extract all words from a cell except the first or the last one, and can be applied to multiple cells in a single step. This method is particularly efficient if you are comfortable working with formulas and want a dynamic solution: whenever the original data changes, the extracted result will update automatically.
Both of the following formulas assume there are no leading or trailing spaces in the data and that words are separated by single spaces. If your data may contain extra spaces, consider using the TRIM() function to remove these before applying the formulas. Also, these formulas do not account for punctuation or line breaks between words—handling such cases may require more advanced formulas or VBA.
Extract all but the first word:
1. In a blank cell (for example, B2), enter the following formula to extract all words except the first:
=RIGHT(A2,LEN(A2)-FIND(" ",A2))2. Press "Enter" to confirm. Then, use the fill handle to drag this formula down to apply it to other rows if needed.
This formula works by finding the first space character in the cell and returning all the text after it, which represents all words except the first one. Note that if the reference cell contains only one word with no spaces, the formula will return an error or an empty cell—double-check your data for such cases.
Extract all but the last word:
1. To extract all words except the last one, enter the following formula into a blank cell (for example, C2):
=LEFT(A2,FIND("[",SUBSTITUTE(A2," ","[",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)2. After entering the formula, press "Enter". Then, drag the fill handle downwards to apply this formula to other cells you wish to process.
This formula relies on replacing the last space in the text string with a unique character (in this case, "["). It then extracts everything to the left of that character, effectively removing the last word. Again, if there is only one word in the cell, the formula may result in an error or an empty result. Double-check for multiple consecutive spaces in your data—use TRIM() if necessary to clean up extra spaces before applying this method.
These formula-based approaches are best suited to users looking for a flexible and easily applied technique, especially for large tables of data. However, they may become less practical if your requirements include more complicated word boundaries, punctuation, or multilingual datasets. In such cases, a more powerful utility or custom scripting may be needed.
Sometimes, you might encounter issues where the formulas return errors such as #VALUE! if the data is not formatted as expected or contains irregular spaces. In these cases, cleaning up the data using Excel's TRIM or SUBSTITUTE functions beforehand can reduce errors.
If you require a solution without formulas or need to extract words based on a variety of patterns, consider using a specialized tool or a custom script as introduced below.
Extract all but the first/last word with Kutools for Excel
For users who prefer to extract text without handling complex formulas, Kutools for Excel offers a convenient alternative. Its Extract Text feature allows for flexible text extraction based on custom patterns and is especially useful for users unfamiliar with Excel functions, or when handling cells with varied patterns and word counts.
Once you have Kutools for Excel installed and enabled, follow these steps to quickly extract all words except the first or last from your selected data:
1. Select the range of cells containing the data from which you want to extract all but the first or last word.
2. Go to the Kutools tab in the ribbon, then click Text > Extract Text. This will open the Extract Text dialog box.
3. In the Extract Text dialog box:
(1) To exclude only the first word, type a space followed by * (that is, *) into the Text box field. To exclude only the last word, type * followed by a space (* ).
(2) Click the Add button to include this extraction pattern in the Extract list box, making sure your pattern displays there as expected. See the screenshot below for a visual reference.
Tip: In the extraction pattern, * represents any sequence of characters, and a space signifies the first found space in the text. Therefore, " *" takes everything after the first space (excluding the first word), and "* " captures everything before the last space (excluding the last word).
4. Click OK. A prompt will appear, asking you to select the destination cell for the extracted result.
5. Confirm by clicking OK again. All selected cells will now display only the words except the first or last, in the location you specified. See the example below for the outcome.
This method is especially advantageous for users processing large or complex datasets and looking to avoid manual formula entry. It also makes extracting patterned text more accessible, reducing the risk of formula errors. However, note that this method is not dynamic—any changes to your source data will not automatically update the extracted results, so consider this when planning your workflow.
While Kutools handles most common text extraction cases quickly, ensure your data follows a standard spacing convention. Unexpected results can occur if input cells contain multiple consecutive spaces or punctuation attached to words. Clean up data with Excel's native trimming tools when necessary to achieve the best results.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Demo: Extract all but the first/last word with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in